package DataManager;

import Constants.PIConstants;
import Service.CharacterObj;
import Service.FarmAnimals;
import Service.FarmLand;
import Service.FarmStuff;
import Utility.CommonUtils;
import java.sql.Timestamp;
import java.sql.Date;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import Shared.PrivateMessage;
import Service.UserObj;
import Shared.GameMapObj;
import Shared.ItemObj;
import Utility.Memcache;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class MySQLCommonDataProvider {

    public static Logger log = LoggerFactory.getLogger(MySQLCommonDataProvider.class.getName());
//    private Connection conn = null;
    private static final int MONEY_TRANSFER_LIMIT = 1000000; // 1mil.
    public static int logDateCurrent = 0; // ngay log dang dung khi ghi xuong DB
    public static int logDateLatest = 0; // ngay log moi nhat duoc cap nhat khi sang ngay moi
    public static int logUserBalanceDateLatest = 0;
    public static int logGameDateLatest = 0;
    public static int logPlayerDateLatest = 0;

    public MySQLCommonDataProvider() {
        if (logDateCurrent == 0) {
            // initialize date variables for DB log-tables
            try {
                java.util.Date now = new java.util.Date();
                String strDateCurrent = String.format("%1$tY%1$tm%1$td", now);
                logDateCurrent = Integer.parseInt(strDateCurrent);
                Calendar today = Calendar.getInstance();
                today.add(Calendar.DATE, -1);
                Date yesterday = new Date(today.getTimeInMillis());
                String strLastetDate = String.format("%1$tY%1$tm%1$td", yesterday);
                logDateLatest = logPlayerDateLatest = logGameDateLatest = logUserBalanceDateLatest = Integer.parseInt(strLastetDate);
                log.info("Date Log initialized !");
            } catch (Exception ex) {
                log.error(MySQLCommonDataProvider.class.getName(), ex);
            }
        }
    }

    public Connection getConnection() {
        Connection conn = null;
        try {
//            log.info("Enter getConnection !");
            //        try {
            //            DbManager connM = new DbManager();
            //            Class.forName("com.mysql.jdbc.Driver").newInstance();
            //            String[] mysqlConnectionString = connM.getConnectionString(0);
            //            conn = DriverManager.getConnection(mysqlConnectionString[0], mysqlConnectionString[1], mysqlConnectionString[2]);
            //        } catch (Exception ex) {
            //            log.info("Couldn't connect to the MySQL database.");
            //            log.error(ex.getClass().getName(), ex);
            //        }
//            log.debug("Connection free: {}",DbManager.connectionPool.getTotalFree());
            conn = DbManager.connectionPool.getConnection(); // fetch a connection


            if (conn == null) {
                log.error(MySQLCommonDataProvider.class.getName(), "Cannot get connection from pool");
            }
        } catch (Exception ex) {
            log.error(MySQLCommonDataProvider.class.getName(), ex);
        }
//        log.info("Exit getConnection!");
        return conn;
    }

    public void closeConnection() {
//        try {
//            conn.close();
//            log.debug("Connection pool free: {}/{}",
//                    DbManager.connectionPool.getTotalFree(),
//                    DbManager.connectionPool.getTotalLeased());
//        } catch (Exception ex) {
//            log.error(ex.getClass().getName(), ex);
//        }
    }

//    public void uGlobalProperty(String propertyName, String propertyValue)
//    {
//        try
//        {
//            if (!(validatePropertyLengths(propertyName, propertyValue)))
//            {
//                return;
//            }
//
//            deleteGlobalProperty(propertyName);
//
//            Statement s = conn.createStatement();
//            s.execute("insert into global_properties values (DEFAULT, '" + propertyName + "', '" + propertyValue + "')");
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//    }
//
//    public void deleteGlobalProperty(String propertyName)
//    {
//        try
//        {
//            if ((propertyName.length() <= 0) || (propertyName.length() > 15))
//            {
//                return;
//            }
//
//            Statement s = conn.createStatement();
//            s.execute("delete from global_properties where property_name='" + propertyName + "'");
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//    }
//
//    public void updateGlobalProperty(String propertyName, String propertyValue)
//    {
//        try
//        {
//            if (!(validatePropertyLengths(propertyName, propertyValue)))
//            {
//                return;
//            }
//
//            Statement s = conn.createStatement();
//            s.execute("update global_properties set property_value='" + propertyValue + "' where property_name='" + propertyName + "'");
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//    }
//
//    public String readGlobalProperty(String propertyName)
//    {
//        String retVal = "";
//
//        if ((propertyName.length() <= 0) || (propertyName.length() > 15))
//        {
//            return retVal;
//        }
//
//        try
//        {
//            Statement s = conn.createStatement();
//            ResultSet rs = s.executeQuery("select property_value from global_properties where property_name='" + propertyName + "'");
//
//            if (rs.next())
//            {
//                retVal = rs.getString(1);
//            }
//
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//
//        return retVal;
//    }
//
//    public void insertProperty(String userName, String propertyName, String propertyValue)
//    {
//        try
//        {
//            if (!(validateUsernameAndPropertyLengths(userName, propertyName, propertyValue)))
//            {
//                return;
//            }
//
//            deleteProperty(userName, propertyName);
//
//            Statement s = conn.createStatement();
//            s.execute("insert into user_properties values (DEFAULT, '" + userName + "', '" + propertyName + "', '" + propertyValue + "')");
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//    }
//
//    public void updateProperty(String userName, String propertyName, String propertyValue)
//    {
//        try
//        {
//            if (!(validateUsernameAndPropertyLengths(userName, propertyName, propertyValue)))
//            {
//                return;
//            }
//
//            Statement s = conn.createStatement();
//            s.execute("update user_properties set property_value='" + propertyValue + "' where username = '" + userName + "' and property_name='" + propertyName + "'");
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//    }
//
//    public void deleteProperty(String userName, String propertyName)
//    {
//        try
//        {
//            if (!(validateUsernamePropertyName(userName, propertyName)))
//            {
//                return;
//            }
//
//            Statement s = conn.createStatement();
//            s.execute("delete from user_properties where property_name='" + propertyName + "' and username = '" + userName + "'");
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//    }
//
//    public String readProperty(String userName, String propertyName)
//    {
//        String retVal = "";
//
//        if (!(validateUsernamePropertyName(userName, propertyName)))
//        {
//            return retVal;
//        }
//
//        try
//        {
//            Statement s = conn.createStatement();
//            ResultSet rs = s.executeQuery("select property_value from user_properties where username = '" + userName + "' and property_name='" + propertyName + "'");
//
//            if (rs.next())
//            {
//                retVal = rs.getString(1);
//            }
//
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//
//        return retVal;
//    }
//
//    public String getAllUserPropertyNames(String userName)
//    {
//        StringBuilder sb = new StringBuilder();
//
//        try
//        {
//            Statement s = conn.createStatement();
//            ResultSet rs = s.executeQuery("select property_name from user_properties where username='" + userName + "'");
//
//            while (rs.next())
//            {
//                sb.append(rs.getString(1) + "~");
//            }
//
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//
//        return sb.toString();
//    }
//
//    public String getAllGlobalPropertyNames()
//    {
//        StringBuilder sb = new StringBuilder();
//
//        try
//        {
//            Statement s = conn.createStatement();
//            ResultSet rs = s.executeQuery("select property_name from global_properties");
//
//            while (rs.next())
//            {
//                sb.append(rs.getString(1) + "~");
//            }
//
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//
//        return sb.toString();
//    }
//    private boolean validateUsernamePropertyName(String userName, String propertyName)
//    {
//        if ((userName.length() <= 0) || (userName.length() > 15))
//        {
//            return false;
//        }
//        if ((propertyName.length() <= 0) || (propertyName.length() > 15))
//        {
//            return false;
//        }
//
//        return true;
//    }
    private boolean validatePropertyLengths(String propertyName, String propertyValue) {
        if ((propertyName.length() <= 0) || (propertyName.length() > 15)) {
            return false;
        }
        if ((propertyValue.length() <= 0) || (propertyValue.length() > 15)) {
            return false;
        }
        return true;
    }

//    private boolean validateUsernameAndPropertyLengths(String userName, String propertyName, String propertyValue)
//    {
//        if (!(validateUsernamePropertyName(userName, propertyName)))
//        {
//            return false;
//        }
//
//        if (propertyValue.length() > 100)
//        {
//            return false;
//        }
//
//        return true;
//    }
    /**
     * Thêm vào danh sách bạn bè
     * @param fromUser
     * @param toUser
     * @return
     */
    //public boolean addFriend(String fromUser,String toUser){
    public boolean addFriend(int fromUserID, int toUserID) {
        boolean result = true;
        try {
            log.info("Enter addFriend");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                //"insert into friend_list values (DEFAULT, '" + fromUser + "', '" + toUser + "')"
                String sql = String.format(
                        "insert into PIDB.friend_list (id, from_userid, to_userid) "
                        + "values (DEFAULT, %d, %d)", fromUserID, toUserID);
                s.execute(sql);

                //Memcache.getInstance().delete("friend-"+fromUser);
                //Memcache.getInstance().delete("friend-"+toUser);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = false;
            } finally {
                s.close();
                conn.close();
                log.info("Exit addFriend");
            }
        } catch (Exception e) {
            log.error("Get Statement error", e.getClass().getName(), e);
            result = false;
        }
        return result;
    }

    //public boolean checkFriend(String fromUser,String toUser){
    public boolean checkFriend(int fromUserID, int toUserID) {
        boolean retValue = false;
        try {
            log.info("Enter checkFriend");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT id FROM PIDB.friend_list "
                        + "WHERE (from_userid=%d AND to_userid=%d) OR (from_userid=%d AND to_userid=%d)",
                        fromUserID, toUserID, toUserID, fromUserID);
                ResultSet rs = s.executeQuery(sql);
                retValue = rs.next();
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit checkFriend");
            }
        } catch (Exception e) {
            log.error("Get Statement error", e.getClass().getName(), e);
        }
        return retValue;
    }

    /**
     * Xóa khỏi danh sách bạn bè
     * @param fromUser
     * @param toUser
     * @return
     */
    //public boolean removeFriend(String fromUser,String toUser){
    public boolean removeFriend(int fromUserID, int toUserID) {
        boolean result = true;
        try {
            log.info("Enter removeFriend");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "delete from PIDB.friend_list "
                        + "where (from_userid=%d AND to_userid=%d) OR (from_userid=%d AND to_userid=%d)",
                        fromUserID, toUserID, toUserID, fromUserID);
                s.execute(sql);
                //s.execute("delete from friend_list where (from_user='"+fromUser+"' AND to_user='"+toUser+"') OR (from_user='"+toUser+"' AND to_user='"+fromUser+"')");
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = false;
            } finally {
                s.close();
                conn.close();
                log.info("Exit removeFriend");
            }
        } catch (Exception e) {
            log.error("Get Statement error", e.getClass().getName(), e);
            result = false;
        }
        return result;
    }

//    public boolean removeChatFriend(int[] friendID, int userID) {
//        boolean result = true;
//        try {
//            log.info("remove chat friend");
//            Connection conn = getConnection();
//            Statement s = conn.createStatement();
//            try {
//                String sql = String.format(
//                        "DELETE FROM PIDB.friend_list WHERE from_userid=%d AND to_userid IN (", userID);
//                for (int i = 0; i < friendID.length; i++) {
//                    sql += String.format("%d", friendID[i]);
//                    if (i == friendID.length - 1) {
//                        sql += ")";
//                    } else {
//                        sql += ",";
//                    }
//                }
//                s.execute(sql);
//            } catch (Exception ex) {
//                log.error(ex.getClass().getName(), ex);
//                result = false;
//            } finally {
//                s.close();
//                conn.close();
//                log.info("Exit removeFriend");
//            }
//        } catch (Exception e) {
//            log.error("Get Statement error", e.getClass().getName(), e);
//            result = false;
//        }
//        return result;
//    }
//    public boolean removePM(int id,String toUser){
//        return true;
//    }
    public ArrayList getFriendList(int userID) {
        ArrayList friends = new ArrayList();
        try {
            log.info("Enter getFriendList");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                //ResultSet rs= s.executeQuery("SELECT friend_list.* FROM friend_list WHERE (friend_list.from_user='"+username+"') OR (friend_list.to_user='"+username+"')" );
                String sql = String.format(
                        "SELECT	friend_list.to_userid, users.username, users.emotion_status, "
                        + "users.money, users.avatar, users.nick_color, users.user_level "
                        + "FROM PIDB.friend_list "
                        + "INNER JOIN users ON users.userid = friend_list.to_userid "
                        + "WHERE friend_list.from_userid = %d "
                        + "UNION "
                        + "SELECT friend_list.from_userid, users.username, users.emotion_status, "
                        + "users.money, users.avatar, users.nick_color, users.user_level "
                        + "FROM PIDB.friend_list "
                        + "INNER JOIN users ON users.userid = friend_list.from_userid "
                        + "INNER JOIN PIDB.characters ON characters.userid = friend_list.from_userid "
                        + "WHERE friend_list.to_userid = %d "
                        + "ORDER BY username", userID, userID);

                ResultSet rs = s.executeQuery(sql);
                int index = 0;
                while (rs.next()) {

                    UserObj userObj = new UserObj();
                    userObj.userid = rs.getInt(1);
                    userObj.username = rs.getString(2);
                    userObj.emotion_status = rs.getString(3);
                    userObj.money = rs.getInt(4);
                    userObj.avatar = rs.getInt(5);
                    userObj.nickColor = rs.getInt(6);
                    userObj.user_level = rs.getInt(7);
//                String fromUser=rs.getString("from_user");
//                String toUser=rs.getString("to_user");
//                if(!fromUser.equals(username)){
//                    userObj.username=fromUser;
//                }else{
//                    userObj.username=toUser;
//                }
                    friends.add(index, userObj);
                    index++;
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit getFriendList");
            }
        } catch (Exception e) {
            log.error("Get Statement error", e.getClass().getName(), e);
        }
        return friends;
    }

    /**
     * Lưu private message
     * @param fromUser
     * @param toUser
     * @param text
     * @return
     */
    public int createMessage(int senderID, int recipientID, String text, int msgType) {
        int lastInsertedId = 0;
        try {
            log.info("Enter createMessage");
            Connection conn = getConnection();
            Statement s = conn.createStatement();

            try {
                text = text.replace("'", "\\'");
                //long timestamp = System.currentTimeMillis()/1000;
                //s.executeUpdate("insert into message values (DEFAULT, '" + fromUser + "',1,1,'" + toUser + "',0,1,0,'"+text+"','"+text+"','"+timestamp+"')",Statement.RETURN_GENERATED_KEYS);
                String sql = String.format(
                        "INSERT INTO PIDB.message (message_id, sender_id, recipient_id, "
                        + "view_status, message_status, message_type, text) "
                        + "VALUES (DEFAULT, %d, %d, 0, 1, %d, '%s')",
                        senderID, recipientID, msgType, text);
                s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                ResultSet rs = s.getGeneratedKeys();
                if (rs != null && rs.next()) {
                    lastInsertedId = rs.getInt(1);
                }
                lastInsertedId = rs.getInt(1);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                //return lastInsertedId;
            } finally {
                s.close();
                conn.close();
                log.info("Exit createMessage");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return lastInsertedId;
    }

    public boolean checkFriendRequestSent(int fromUserID, int toUserID) {
        boolean result = true;
        try {
            log.info("Enter createMessage");
            Connection conn = getConnection();
            Statement s = conn.createStatement();

            try {
                String sql = String.format("SELECT COUNT(*) "
                        + "FROM PIDB.message "
                        + "WHERE sender_id=%d AND recipient_id=%d AND message_type=%d AND message_status=1",
                        fromUserID, toUserID, PIConstants.MESSAGE_TYPE_FRIEND_REQUEST);
                ResultSet rs = s.executeQuery(sql);
                if (rs != null && rs.next()) {
                    result = (rs.getInt(1) == 1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    /**
     * Lấy inbox message
     * @param currentUser
     * @return
     */
    public ArrayList getInboxMessage(int userID) {
        ArrayList msgList = new ArrayList();
        try {
            log.info("Enter getAdminMessage");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT "
                        + "users.username, "
                        + "message.message_id, "
                        + "message.sender_id, "
                        + "message.recipient_id, "
                        + "message.message_status, "
                        + "message.message_type, "
                        + "message.text, "
                        + "message.sent_time "
                        + "FROM PIDB.message "
                        + "INNER JOIN users ON users.userid = PIDB.message.sender_id "
                        + "WHERE (message_status=1) AND (recipient_id=%d) "
                        + "ORDER BY sent_time DESC",
                        userID);
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    PrivateMessage pmObj = new PrivateMessage();
                    pmObj.messageId = rs.getInt("message.message_id");
                    pmObj.senderId = rs.getInt("sender_id");
                    pmObj.senderName = rs.getString("username");
                    pmObj.text = rs.getString("message.text");
                    pmObj.msgType = rs.getByte("message.message_status");
                    pmObj.type = rs.getByte("message.message_type");
                    Timestamp postDate = rs.getTimestamp("sent_time");
                    pmObj.sentTime = CommonUtils.ParseSQLTimestamp(postDate);
                    msgList.add(pmObj);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit getInboxMessage");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return msgList;
    }

    public boolean updateMessageViewStatus(int id, int status) {
        boolean result = true;
        try {
            log.info("Enter updateMessageViewStatus");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String sql = String.format("update message set view_status=%d where message_id=%d", status, id);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = false;
            } finally {
                s.close();
                conn.close();
                log.info("Exit updateMessageViewStatus");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = false;
        }
        return result;
    }

    public boolean updateMessageStatus(int id, int status) {
        boolean result = true;
        try {
            log.info("Enter updateMessageStatus");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("update PIDB.message set message_status=%d where message_id = %d", status, id);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = false;
            } finally {
                s.close();
                conn.close();
                log.info("Exit updateMessageStatus");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = false;
        }
        return result;
    }

    public int getNickColor(int userID) {
        int nickColor = 0xFFFFFF;
        try {
            log.info("Enter getNickColor");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("select nick_color from users where userid=%d", userID);
                ResultSet rsa = s.executeQuery(sql);
                if (rsa.next()) {
                    nickColor = rsa.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getNickColor");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return nickColor;
    }

    public int getUserAvatar(int userID) {
        int id = 0;
        try {
            log.info("Enter getUserAvatar");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("select avatar from users where userid=%d", userID);
                ResultSet rsa = s.executeQuery(sql);
                if (rsa.next()) {
                    id = rsa.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserAvatar");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return id;
    }

    public int changeUserAvatar(int userID, int avatarID) {
        int result = -1;
        try {
            log.info("Enter changeUserAvatar");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "update users set avatar = %d where userid = %d",
                        avatarID, userID);
                s.execute(sql);
                VCoreUserDataProvider.getInstance().AddAvatar(userID, 1, avatarID, 0, 1);
                result = avatarID;
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit changeUserAvatar");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public InputStream getItemImage(int itemID) {
        InputStream stream = null;
        try {
            log.info("Enter getAvatarById");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                ResultSet rsa = s.executeQuery("select image from PIDB.items where item_id='" + itemID + "'");
                if (rsa.next()) {
                    stream = rsa.getBinaryStream("image");
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getAvatarById");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return stream;
    }

    public int addUserInventory(int UserID, int itemID, int status) {
        int result = 1;
        try {
            log.info("Enter addUserInventory");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                int duration = 0;
                int usages = 0;
                Date expired = null;
                // "select duration, expired_date from items where id='"+itemID+"'"
                String sql = String.format(
                        "select duration, expired_date, usages from PIDB.items where item_id=%d", itemID);
                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    duration = rs.getInt(1);
                    expired = rs.getDate(2);
                    usages = rs.getInt(3);
                }

                sql = String.format(
                        "INSERT INTO PIDB.user_inventory("
                        + "inventory_id, user_id, item_id, status, creation_date, expired_date, usages) "
                        + " VALUES (DEFAULT, %d, %d, %d, NOW(), ", UserID, itemID, status);

                if (expired != null) {
                    sql += "'" + expired + "', ";
                } else {
                    if (duration == 0) {
                        sql += "null, ";
                    } else {
                        sql += String.format("ADDTIME(NOW(),'%d:00:00'), ", duration);
                    }
                }

                if (usages > 0) {
                    sql += "'" + usages + "')";
                } else {
                    sql += "null)";
                }

                log.info(sql);
                s.execute(sql);
                VCoreUserDataProvider.getInstance().AddItem(UserID, "add item", itemID);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit addUserInventory");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    public byte removeUserInventory(int itemID) {
        byte result = 1;
        try {
            log.info("Enter removeUserInventory");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "DELETE FROM PIDB.user_inventory WHERE id=%d", itemID);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit removeUserInventory");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    public byte updateUserInventoryStatus(int inventoryID, int status) {
        byte result = 1;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "UPDATE PIDB.user_inventory SET status=%d "
                        + "WHERE inventory_id=%d", status, inventoryID);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit update user inventory status");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    public byte updateUserItemUsages(int userID, int inventoryID, int usages) {
        byte result = 1;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "UPDATE PIDB.user_inventory SET usages=%d "
                        + "WHERE inventory_id=%d AND user_id=%d", usages, inventoryID, userID);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit update user inventory status");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    public ArrayList getShopItemList(int shopID) {
        ArrayList itemList = new ArrayList();
        try {
            log.info("Enter getShopItemList");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT items.item_id, "
                        + "items.name, "
                        + "items.money, "
                        + "items.vip_money, "
                        + "items.item_category, "
                        + "items.duration, "
                        + "items.expired_date, "
                        + "items.usages, "
                        + "items.description, "
                        + "items.level_required "
                        + "FROM PIDB.items, PIDB.shop_items, PIDB.shop "
                        + "WHERE (items.active = '1') AND (shop.shop_id = %d) "
                        + "AND (items.item_id = shop_items.item_id) "
                        + "AND (shop.shop_id = shop_items.shop_id)", shopID)
                        + "ORDER BY item_id";

                ResultSet rs = s.executeQuery(sql);
                Date date;
                while (rs.next()) {
                    ItemObj item = new ItemObj();
                    item.id = rs.getInt("item_id");
                    item.category = shopID;
                    //item.image=rs.getString("image");
                    item.name = rs.getString("name");
                    item.money = rs.getInt("money");
                    item.vipMoney = rs.getInt("vip_money");
                    item.category = rs.getInt("item_category");
                    item.duration = rs.getInt("duration");
                    date = rs.getDate("expired_date");
                    item.expired_date = CommonUtils.ParseSQLDate(date);
                    item.usages = rs.getInt("usages");
                    item.description = rs.getString("description");
                    item.level = rs.getInt("level_required");
                    itemList.add(item);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit getShopItemList");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return itemList;
    }

    public boolean checkAvatarVIP(int avartaID) {
        boolean retValue = false;
        try {
            log.info("Enter checkAvatarVIP");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("select id, name, category "
                        + "from items "
                        + "where (active='1') and (id=%d) ", avartaID)
                        + "ORDER BY id";
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    if (rs.getInt("category") == 1) {
                        retValue = true;
                        break;
                    }
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit checkAvatarVIP");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return retValue;
    }

    public int getItemCost(int itemID, boolean isVIP) {
        int money = 0;
        String money_field = "money";
        if (isVIP) {
            money_field = "vip_money";
        }

        try {
            log.info("Enter getItemCost");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                ResultSet rs = s.executeQuery("select " + money_field + " from PIDB.items where item_id='" + itemID + "'");
                if (rs.next()) {
                    money = rs.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getItemCost");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return money;
    }

    public int getItemHealthIndex(int itemID) {
        int money = 0;
        try {
            log.info("Enter getItemCost");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = "select money from PIDB.items where item_id=" + itemID;
                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    money = rs.getInt(1);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getItemCost");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return money;
    }

    public int getItemDuration(int itemID) {
        int duration = 0;
        try {
            log.info("Enter getItemDuration");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                ResultSet rs = s.executeQuery("select duration from items where id='" + itemID + "'");
                if (rs.next()) {
                    duration = rs.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getItemDuration");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }

        return duration;
    }

    public ArrayList getMapList() {
        ArrayList listRoom = new ArrayList();
        try {
            log.info("Enter getMapList");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = "select id, map_code, sub_map_code, name, type, money, zone "
                        + "from PIDB.game_room "
                        //                        + "where game=" + gameType + " "
                        + "order by type, map_code, sub_map_code ASC";
                ResultSet rs = s.executeQuery(strSQL);
                while (rs.next()) {
                    //int id=rs.getInt("id");
                    int code = rs.getInt("map_code") * 10000 + rs.getInt("sub_map_code") * 100;
                    String name = rs.getString("name");
                    int type = rs.getInt("type");
                    int money = rs.getInt("money");
                    int zone = rs.getInt("zone");
                    GameMapObj gm = new GameMapObj(code, name);
                    gm.money = money;
                    gm.type = type;
                    gm.noOfZones = zone;
                    listRoom.add(gm);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getMapList");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return listRoom;
    }

//    public ArrayList getChatRoomList(int gameType) {
//        ArrayList listRoom = new ArrayList();
//        try {
//            log.info("Enter getChatRoomList");
//            Connection conn = getConnection();
//            Statement s = conn.createStatement();
//            try {
//                ResultSet rs = s.executeQuery("select id, code, name, type, money from tl_room where game=" + gameType + " order by type, code ASC");
//                while (rs.next()) {
//                    String code = rs.getString("code");
//                    String name = rs.getString("name");
//                    int type = rs.getInt("type");
//                    ChatRoom cr = new ChatRoom(code, name, type);
//                    listRoom.add(cr);
//                }
//            } catch (Exception ex) {
//                log.error(ex.getClass().getName(), ex);
//            } finally {
//                s.close();
//                conn.close();
//                log.info("Exit getChatRoomList");
//            }
//        } catch (Exception e) {
//            log.error("Get Statement error : ", e.getClass().getName(), e);
//        }
//        return listRoom;
//    }
    public ArrayList getBestPlayer() {
        ArrayList playerList = new ArrayList();
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                log.info("Enter getBestPlayer");
                String sqlStatement =
                        "SELECT users.userid, ((total_won*2)-total_game) as rate FROM user_playing_stat "
                        + "INNER JOIN users ON users.userid = user_playing_stat.userid "
                        + "WHERE (users.userid  <> 5125) "
                        + " AND (is_active='Y') "
                        + "GROUP BY user_playing_stat.userid "
                        + "ORDER BY rate DESC LIMIT 10";
                ResultSet rs = s.executeQuery(sqlStatement);

                while (rs.next()) {
                    int userID = rs.getInt(1);
                    String username = VCoreUserDataProvider.getInstance().GetUserNamebyId(userID);
                    int rate = rs.getInt(2);
                    ArrayList player = new ArrayList();
                    player.add(userID);
                    player.add(username);
                    player.add(rate);
                    playerList.add(player);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getBestPlayer");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return playerList;
    }

    public ArrayList getMostWealthyPlayer() {
        ArrayList playerList = new ArrayList();
        try {
            log.info("Enter getMostWealthyPlayer");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlStatement =
                        "SELECT userid, username, money "
                        + "FROM users "
                        + "WHERE (userid <> 5125) "
                        + " AND (is_active='Y') "
                        + " AND userid NOT IN( "
                        + "      SELECT user_id FROM vivoo_center.core_users "
                        + "      WHERE user_type = 3) "
                        + "ORDER BY money "
                        + "DESC LIMIT 10";
                ResultSet rs = s.executeQuery(sqlStatement);

                while (rs.next()) {
                    int userID = rs.getInt(1);
                    String username = rs.getString(2);
                    int money = rs.getInt(3);
                    if (rs.getInt(3) >= Integer.MAX_VALUE) {
                        money = 0; // don't support money larger than 4.2B
                    };
                    ArrayList player = new ArrayList();
                    player.add(userID);
                    player.add(username);
                    player.add(money);
                    playerList.add(player);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getMostWealthyPlayer");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return playerList;
    }

    public boolean updateGameHistory(String userName, int result, int gameID, int dateLog, int game_service) {
        int userID = VCoreUserDataProvider.getInstance().GetUserIdByUsername(userName);
        return updateGameHistory(userID, result, gameID, dateLog, game_service);
    }

    public boolean updateGameHistory(int userID, int result, int gameID, int dateLog, int game_service) {
        boolean resultUpdate = true;
        try {
            log.info("Enter updateGameHistory");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Statement s1 = conn.createStatement();
            Statement s2 = conn.createStatement();
            try {
                if (logDateCurrent != logPlayerDateLatest) {
                    // create new tables
                    String searchTable = String.format("SELECT table_name "
                            + "FROM information_schema.tables "
                            + "WHERE table_schema = 'gfsserver' "
                            + "AND table_name = 'best_player_%s'", logDateCurrent);
                    ResultSet rs = s1.executeQuery(searchTable);
                    if (!rs.next()) {
                        String sql = String.format(
                                "CREATE TABLE best_player_%s LIKE best_player;",
                                logDateCurrent);
                        s2.execute(sql);
                        logPlayerDateLatest = logDateCurrent;
                    }
                }
                // result: 1: win; 0: draw; -1: lose
                String sqlStatement = String.format(
                        "INSERT INTO best_player_%s (userid, game_id, result, play_date, log_game_id) "
                        + "VALUES (%d, %d, %d, NOW(), %d)", dateLog > 0 ? dateLog : logDateCurrent, userID, game_service, result, gameID);
                boolean ret = s.execute(sqlStatement);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                resultUpdate = false;
            } finally {
                s.close();
                s1.close();
                s2.close();
                conn.close();
                log.info("Exit updateGameHistory");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            resultUpdate = false;
        }
        return resultUpdate;
    }

    public int getAvatarListCount() {
        int result = 0;
        try {
            log.info("Enter getAvatarListCount");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                ResultSet rs = s.executeQuery("select count(id) from items where active='1'");
                if (rs.next()) {
                    result = rs.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getAvatarListCount");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

//    public ItemImageRequest getItemImage(int itemID, String itemName, byte requestType) {
//        ItemImageRequest itemImage = null;
//        try {
//            log.info("Enter getItemImage");
//            Connection conn = getConnection();
//            Statement s = conn.createStatement();
//            try {
//                String sqlStatement = "select id, name, ";
//                switch (requestType) {
//                    case 0:
//                        sqlStatement += "image";
//                    case 1:
//                        sqlStatement += "thumbnail";
//                    case 2:
//                        sqlStatement += "image, thumbnail";
//                }
//                sqlStatement += " from items where ";
//                if (itemID >= 0) {
//                    sqlStatement += "id='" + itemID + "'";
//                } else if (itemName != null) {
//                    sqlStatement += "name='" + itemName + "'";
//                } else {
//                    itemImage = null;
//                }
//
//
//                ResultSet rsa = s.executeQuery(sqlStatement);
//                if (rsa.next()) {
//                    itemImage = new ItemImageRequest();
//                    itemImage.itemID = rsa.getInt("id");
//                    itemImage.itemName = rsa.getString("name");
//                    if ((requestType == 0) || (requestType == 2)) {
//                        itemImage.streamImage = rsa.getBinaryStream("image");
//                    }
//                    if ((requestType == 1) || (requestType == 2)) {
//                        itemImage.streamThumbImage = rsa.getBinaryStream("thumbnail");
//                    }
//                }
//            } catch (Exception ex) {
//                log.error(ex.getClass().getName(), ex);
//            } finally {
//                s.close();
//                conn.close();
//                log.info("Exit getItemImage");
//            }
//        } catch (Exception e) {
//            log.error("Get Statement error : ", e.getClass().getName(), e);
//        }
//        return itemImage;
//    }
    public ArrayList getUserInventory(int userID) {
        ArrayList listItems = new ArrayList();
        Date date;
        try {
            log.info("Enter getUserInventory");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT "
                        + "user_inventory.inventory_id, "
                        + "user_inventory.item_id, "
                        + "user_inventory.creation_date, "
                        + "user_inventory.expired_date, "
                        + "user_inventory.usages, "
                        + "user_inventory.status, "
                        + "items.item_category, "
                        + "items.name, "
                        + "items.level_required "
                        + "FROM "
                        + "PIDB.user_inventory "
                        + "INNER JOIN PIDB.items ON items.item_id = user_inventory.item_id "
                        + "WHERE "
                        + "(user_inventory.user_id = %d) "
                        + "AND ((user_inventory.expired_date > NOW()) OR (ISNULL(user_inventory.expired_date))) "
                        //                        + "AND ((user_inventory.usages > 0) OR (ISNULL(user_inventory.usages))) "
                        + "ORDER BY inventory_id DESC",
                        userID);
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    ItemObj item = new ItemObj();
                    item.id = rs.getInt("item_id");
                    item.inventory_id = rs.getInt("inventory_id");
                    item.name = rs.getString("name");
                    item.category = rs.getInt("item_category");
                    date = rs.getDate("creation_date");
                    item.creation_date = CommonUtils.ParseSQLDate(date);
                    date = rs.getDate("expired_date");
                    item.expired_date = CommonUtils.ParseSQLDate(date);
                    item.usages = rs.getInt("usages");
                    item.status = rs.getInt("status");
                    item.level = rs.getInt("level_required");
                    listItems.add(item);
                }
            } catch (Exception ex) {
                listItems = null;
                ex.printStackTrace();
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserInventory");
            }
        } catch (Exception e) {
            listItems = null;
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return listItems;
    }

    public int getUserUnusedItem(int userID) {
        int count = 0;
        try {
            log.info("Enter getUserInventory");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT COUNT(inventory_id) "
                        + "FROM PIDB.user_inventory "
                        + "WHERE (user_id = %d) "
                        + "AND (status = 0) "
                        + "AND ((expired_date > NOW()) OR (ISNULL(expired_date))) "
                        + "AND ((usages > 0) OR (ISNULL(usages)))",
                        userID);
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    count = rs.getInt(1);
                }
            } catch (Exception ex) {
                count = -1;
                ex.printStackTrace();
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserInventory");
            }
        } catch (Exception e) {
            count = -1;
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return count;
    }

    public ItemObj getUserItem(int inventoryID) {
        ItemObj item = null;
        try {
            log.info("Enter getUserInventory");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT user_inventory.item_id, "
                        + "user_inventory.creation_date, "
                        + "user_inventory.expired_date, "
                        + "user_inventory.status, "
                        + "items.item_category, "
                        + "items.name, "
                        + "items.health_index, "
                        + "items.stylish_index, "
                        + "items.level_required "
                        + "FROM "
                        + "PIDB.user_inventory "
                        + "INNER JOIN PIDB.items ON items.item_id = user_inventory.item_id "
                        + "WHERE "
                        + "(user_inventory.inventory_id = %d) "
                        + "AND ((user_inventory.expired_date > NOW()) OR (ISNULL(user_inventory.expired_date))) "
                        + "AND ((user_inventory.usages > 0) OR (ISNULL(user_inventory.usages))) ",
                        inventoryID);
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    item = new ItemObj();
                    item.inventory_id = inventoryID;
                    item.id = rs.getInt("item_id");
                    item.name = rs.getString("name");
                    item.category = rs.getInt("item_category");
                    Date date = rs.getDate("creation_date");
                    item.creation_date = CommonUtils.ParseSQLDate(date);
                    date = rs.getDate("expired_date");
                    item.expired_date = CommonUtils.ParseSQLDate(date);
                    item.status = rs.getInt("status");
                    item.healthIndex = rs.getInt("health_index");
                    item.styleIndex = rs.getInt("stylish_index");
                    item.level = rs.getInt("level_required");
                }
            } catch (Exception ex) {
                item = null;
                log.error(ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserInventory");
            }
        } catch (Exception e) {
            item = null;
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return item;
    }

    //Check neu item co trong inventory cua user
    public int countInventoryItem(int userID, int inventory_id) {
        int retVal = 0;
        try {
            log.info("Enter countInventoryItem");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT *"
                        + "FROM PIDB.user_inventory "
                        + "WHERE "
                        + "user_id = %d "
                        + "AND id = %d ",
                        userID, inventory_id);

                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    retVal = 1;
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit countInventoryItem");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return retVal;
    }

    public String getUserEmotionStatus(int userID) {
        String result = "";
        try {
            log.info("Enter getUserEmotionStatus");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                ResultSet rsa = s.executeQuery("select emotion_status from users where userid=" + userID);
                if (rsa.next()) {
                    result = rsa.getString(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserEmotionStatus");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public boolean setUserEmotionStatus(int userID, String status) {
        boolean result = false;
        try {
            log.info("Enter setUserEmotionStatus");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                s.execute("update users set emotion_status = '" + status + "' where userid=" + userID);
                result = true;
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit setUserEmotionStatus");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public int getUserLevel(int userID) {
        int result = 0;
        try {
            log.info("Enter getUserLevel");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                ResultSet rsa = s.executeQuery("select user_level from users where userid=" + userID);
                if (rsa.next()) {
                    result = rsa.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserLevel");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public boolean setUserLevel(int userID, int level) {
        boolean result = false;
        try {
            log.info("Enter setUserLevel");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                s.execute("update users set user_level = " + level + " where userid=" + userID);
                result = true;
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit setUserLevel");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }

        return result;
    }

    public boolean setNickColor(int userID, int nickColor) {
        boolean result = false;
        try {
            log.info("Enter setNickColor");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                s.execute("update users set nick_color = '" + nickColor + "' where userid=" + userID);
                result = true;
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit setNickColor");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }
//    public UserObj getUserDisplayInfo(int userID) {
//        UserObj result = null;
//        try
//        {
//            Statement s = conn.createStatement();
//            ResultSet rs = s.executeQuery(
//                    "select avatar, nick_color, emotion_status, user_level, money "
//                    + "from users "
//                    + "where userid=" + userID );
//            if (rs.next())
//            {
//                result = new UserObj();
//                result.avatar = rs.getInt(1);
//                result.nickColor = rs.getInt(2);
//                result.emotion_status = rs.getString(3);
//                result.user_level = rs.getInt(4);
//                result.money = rs.getInt(5);
//            }
//            s.close();
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//        }
//        return result;
//    }

    public UserObj getUserServiceInfoByUserID(int userID) {
        UserObj result = new UserObj();
        try {
            log.info("Enter getUserServiceInfoByUserID");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT users.avatar, users.nick_color, users.emotion_status, "
                        + "users.user_level, users.money, users.is_active, core_users.user_type "
                        + "FROM users "
                        + "INNER JOIN vivoo_center.core_users ON users.userid = vivoo_center.core_users.user_id "
                        + "WHERE users.userid=%d", userID);
                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    result.avatar = rs.getInt(1);
                    result.nickColor = rs.getInt(2);
                    result.emotion_status = rs.getString(3);
                    result.user_level = rs.getInt(4);
                    result.money = rs.getInt(5);
                    result.is_active = rs.getString(6).charAt(0);
                    result.user_type = rs.getInt(7);
                } else {
                    result = null;
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserServiceInfoByUserID");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public UserObj getUserServiceInfoByUserName(String userName) {
        UserObj result = new UserObj();
        try {
            log.info("Enter getUserServiceInfoByUserName");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT users.avatar, users.nick_color, users.emotion_status, "
                        + "users.user_level, users.money, users.is_active, core_users.user_type "
                        + "FROM users "
                        + "INNER JOIN vivoo_center.core_users ON users.userid = vivoo_center.core_users.user_id "
                        + "WHERE username='%s'", userName);
                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    result.avatar = rs.getInt(1);
                    result.nickColor = rs.getInt(2);
                    result.emotion_status = rs.getString(3);
                    result.user_level = rs.getInt(4);
                    result.money = rs.getInt(5);
                    result.is_active = rs.getString(6).charAt(0);
                    result.user_type = rs.getInt(7);
                } else {
                    result = null;
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUserServiceInfoByUserName");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public ArrayList getSMSSyntax(int userID, String regNumber) {
        ArrayList smsSyntax = null;
        try {
            log.info("Enter getSMSSyntax");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Statement s1 = conn.createStatement();
            try {
                // get parter id
                int partnerId = 1; // default registration provider is MSS
                String sql;

                // 2011/7/3 - Nhan  - Fix bug returning wrong SMS # when user firstly registers
                if (userID == 0) {
                    // new user
                    sql = String.format("SELECT partner_id FROM partners "
                            + "WHERE SMS_REG_NO = '%s'", regNumber);
                } else {
                    // user existed, not new user
                    sql = String.format("SELECT partner_id"
                            + " FROM user_originated"
                            + " WHERE userid = %d ", userID);
                }

                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    partnerId = rs.getInt("partner_id");
                }

                // switch users from 8x40 MSS --> 8x79 VMG
//                if (partnerId == 1) {
//                    partnerId = 4;
//                }

                // get SMS Syntax from partner id
                sql = String.format("SELECT SMS_REG_NO, SMS_REG_SYNTAX, SMS_TOPUP_NO, "
                        + "SMS_TOPUP_SYNTAX, SMS_BLOCK_NICK_NO, SMS_BLOCK_NICK_SYNTAX, "
                        + "SMS_TURNON_NICK_NO, SMS_TURNON_NICK_SYNTAX, SMS_RESETPASS_NICK_NO, "
                        + "SMS_RESETPASS_NICK_SYNTAX, SMS_VIP_NO, SMS_VIP_SYNTAX, UPDATE_URL "
                        + "FROM partners"
                        //                    + ", user_originated "
                        + " WHERE "
                        //                    + "user_originated.userid = %d "
                        //                    + "AND user_originated.partner_id = partners.partner_id "
                        //                    + "AND "
                        + "partner_id = %d ",
                        //                    userID,
                        partnerId);
                rs = s1.executeQuery(sql);

                if (rs.next()) {
                    String regNo = rs.getString("SMS_REG_NO");

                    // check SMS Syntax
//                    if ((userID == 0) || (!regNo.equals(regNumber))) {
                    log.info("UPDATING SMS Syntax ({})", regNumber);
                    String regSyntax = rs.getString("SMS_REG_SYNTAX");
                    String topupNo = rs.getString("SMS_TOPUP_NO");
                    String topupSyntax = rs.getString("SMS_TOPUP_SYNTAX");
                    String blockNo = rs.getString("SMS_BLOCK_NICK_NO");
                    String blockSyntax = rs.getString("SMS_BLOCK_NICK_SYNTAX");
                    String unblockNo = rs.getString("SMS_TURNON_NICK_NO");
                    String unblockSyntax = rs.getString("SMS_TURNON_NICK_SYNTAX");
                    String resetPassNo = rs.getString("SMS_RESETPASS_NICK_NO");
                    String resetPassSyntax = rs.getString("SMS_RESETPASS_NICK_SYNTAX");
                    String regVipNo = rs.getString("SMS_VIP_NO");
                    String regVipSyntax = rs.getString("SMS_VIP_SYNTAX");
                    String updateUrl = rs.getString("UPDATE_URL");
                    smsSyntax = new ArrayList();
                    smsSyntax.add(regNo);
                    smsSyntax.add(regSyntax);
                    smsSyntax.add(topupNo);
                    smsSyntax.add(topupSyntax);
                    smsSyntax.add(blockNo);
                    smsSyntax.add(blockSyntax);
                    smsSyntax.add(unblockNo);
                    smsSyntax.add(unblockSyntax);
                    smsSyntax.add(resetPassNo);
                    smsSyntax.add(resetPassSyntax);
                    smsSyntax.add(regVipNo);
                    smsSyntax.add(regVipSyntax);
                    smsSyntax.add(updateUrl);
//                    } else {
//                        // return null;
//                        // 2011/7/2 - Nhan  - Fix bug not closing DB connection
//                        smsSyntax = null;
//                    }
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                smsSyntax = null;
            } finally {
                s.close();
                conn.close();
                log.info("Exit getSMSSyntax");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            smsSyntax = null;
        }
        return smsSyntax;
    }

    public boolean checkSMSSyntax(int userID, String regNumber) {
        boolean result = true;
        ArrayList smsSyntax = null;
        try {
            log.info("Enter checkSMSSyntax");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("SELECT SMS_REG_NO "
                        + "FROM partners, user_originated "
                        + " WHERE "
                        + "user_originated.userid = %d "
                        + "AND user_originated.partner_id = partners.partner_id "
                        //                    + "AND "
                        + "partners.SMS_REG_NO = '%s'",
                        //                    userID,
                        regNumber);
                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    String regNo = rs.getString("SMS_REG_NO");
                    String regSyntax = rs.getString("SMS_REG_SYNTAX");
                    String topupNo = rs.getString("SMS_TOPUP_NO");
                    String topupSyntax = rs.getString("SMS_TOPUP_SYNTAX");
                    String blockNo = rs.getString("SMS_BLOCK_NICK_NO");
                    String blockSyntax = rs.getString("SMS_BLOCK_NICK_SYNTAX");
                    String unblockNo = rs.getString("SMS_TURNON_NICK_NO");
                    String unblockSyntax = rs.getString("SMS_TURNON_NICK_SYNTAX");
                    String resetPassNo = rs.getString("SMS_RESETPASS_NICK_NO");
                    String resetPassSyntax = rs.getString("SMS_RESETPASS_NICK_SYNTAX");
                    String regVipNo = rs.getString("SMS_VIP_NO");
                    String regVipSyntax = rs.getString("SMS_VIP_SYNTAX");
                    String updateUrl = rs.getString("UPDATE_URL");
                    smsSyntax = new ArrayList();
                    smsSyntax.add(regNo);
                    smsSyntax.add(regSyntax);
                    smsSyntax.add(topupNo);
                    smsSyntax.add(topupSyntax);
                    smsSyntax.add(blockNo);
                    smsSyntax.add(blockSyntax);
                    smsSyntax.add(unblockNo);
                    smsSyntax.add(unblockSyntax);
                    smsSyntax.add(resetPassNo);
                    smsSyntax.add(resetPassSyntax);
                    smsSyntax.add(regVipNo);
                    smsSyntax.add(regVipSyntax);
                    smsSyntax.add(updateUrl);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = false;
            } finally {
                s.close();
                conn.close();
                log.info("Exit checkSMSSyntax");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = false;
        }

        return result;
    }

    public int retrieveAccountBalance(String userName) {
        int balanceReturn = -1;
        try {
            log.info("Enter retrieveAccountBalance - by username");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("select money from users where username='%s'", userName);
                ResultSet rsa = s.executeQuery(sql);
                if (rsa.next()) {
                    balanceReturn = rsa.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit retrieveAccountBalance - by username");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return balanceReturn;
    }

    public int retrieveAccountBalance(int userID) {
        int balanceReturn = -1;
        try {
            log.info("Enter retrieveAccountBalance - by userid");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("select money from users where userid=%d", userID);

                ResultSet rsa = s.executeQuery(sql);
                if (rsa.next()) {
                    balanceReturn = rsa.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit retrieveAccountBalance - by userid");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return balanceReturn;
    }

    public boolean setUserMoney(String userName, int money, String description, int addmoney, int type, int serviceID, int logGameID) {
        int userID = VCoreUserDataProvider.getInstance().GetUserIdByUsername(userName);
        return setUserMoney(userID, money, description, addmoney, type, serviceID, logGameID);
//        try
//        {
//            log.info("Update new balance for "+userName+": "+money);
//            if(money<0) money=0;
//            //switchConnection(1);
//            Statement s = conn.createStatement();
//            s.execute("update users set money='" + money + "' where username='" + userName+"'");
//            s.close();
//            //Memcache.getInstance().delete("balance-"+userName);
//            //Memcache.getInstance().set("balance-"+userName,3600,money);
//
//        }
//        catch (Exception ex)
//        {
//            log.error(ex.getClass().getName(),ex);
//            return false;
//        }
//        return true;
    }

    public boolean setUserMoney(int userID, int money, String description, int addmoney, int type, int serviceId, int logGameID) {
        boolean result = true;
        try {
            if (money < 0) {
                money = 0;
            }
            //switchConnection(1);
            log.info("Enter setUserMoney");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                s.execute("update users set money='" + money + "' where UserID=" + userID);
                //Memcache.getInstance().delete("balance-"+userName);
                //Memcache.getInstance().set("balance-"+userName,3600,money);
                logUserBalance(userID, serviceId, money, description, addmoney, type, logGameID);
//- addmoney: lượng tiền thay đổi (dương là thêm vào; âm là trừ đi)
//- type: kiểu thay đổi, mang các giá trị như sau
//            0: người chơi ăn thua với nhau (player-player), chuyển từ người này sang người khác
//            1: có sự thay đổi bằng tay (admin điều chỉnh database, cho/tặng tiền)
//            2: từ chức năng chuyển tiền trong game
//            3 : Tiền xâu
//            4 : Nhận ân nghĩa giang hồ
//            5 : Mua item và avartar
//            6 : Thưởng đăng nhập mỗi ngày, và đăng nhập may mắn (lần thứ ?x500)
//            7 : Event giới thiệu người chơi mới
//            10: nạp tiền từ sms
//            11: nạp tiền từ thẻ
//            100: khuyến mãi từ hệ thống (hàng ngày)
//            101: khuyến mãi từ hệ thống (do nạp thẻ)
//            102: khuyến mãi từ hệ thống (do giới thiệu người chơi)
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = false;
            } finally {
                s.close();
                conn.close();
                log.info("Exit setUserMoney");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = false;
        }
        return result;
    }

    public void updateUserID() {
        String userName = null;
        int userID;
        try {
            log.info("Enter updateUserID");
            String str = VCoreUserDataProvider.getInstance().GetUserListMobile();
            ArrayList list = CommonUtils.stringToArrayList(str, "~");
            for (int i = 0; i < list.size(); i++) {
                userID = VCoreUserDataProvider.getInstance().GetUserIdByUsername((String) list.get(i));
                if (userID > 0) {
                    UserObj user = getUserServiceInfoByUserID(userID);
                    if (user != null) {
                        continue;
                    }

                    user = new UserObj();
                    user.userid = userID;
                    user.username = (String) list.get(i);
                    user.avatar = (int) Math.round(Math.random() * 10);
                    user.money = 0;
                    log.info("Create user: {} (ID:{})... ", user.username, user.userid);
                    createNewUserRecord(user);
//                    log.info("Done");
                }

            }
//            Statement s = conn.createStatement();
//            ResultSet rsa = s.executeQuery("SELECT id,userid from best_player where userid is null");
//            while (rsa.next())
//            {
//                 userName = rsa.getString(2);
//                 try {
//                userID=VCoreUserDataProvider.getInstance().GetUserIdByUsername(userName);
//
//                }
//                 catch (Exception e) {
//                     userID = 0;
//                 }
//                if (userID == 0) continue;
//                Statement stUpdate = conn.createStatement();
//                stUpdate.execute("update best_player set userid='" + userID + "' where id = '" +  rsa.getString(1) + "'");
//                stUpdate.close();
//
//                log.info("ID = " + rsa.getString(1) + " - UserID = "+userID+" - UserName = "+userName);
//            }
//            s.close();
        } catch (Exception ex) {
            log.info(userName);
            log.error(ex.getClass().getName(), ex);
        }
        log.info("Exit updateUserID");
    }

    public void createNewUserRecord(UserObj user) {
        try {
            log.info("Enter createNewUserRecord");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                // result: 1: win; 0: draw; -1: lose
                String sqlStatement = String.format(
                        "INSERT INTO users (userid, username, last_login, is_active, money, avatar) "
                        + "VALUES (%d, '%s', NOW(), 'N', %d, %d)",
                        user.userid, user.username, user.money, user.avatar);

                boolean ret = s.execute(sqlStatement);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit createNewUserRecord");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public int getUnreadMessage(int userID) {
        int result = 0;
        try {
            log.info("Enter getUnreadMessage");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "SELECT COUNT(message_id) "
                        + "FROM message "
                        + "WHERE	(toview=0) AND (tostatus=1) AND (message.to_userid = %d);",
                        userID);
                ResultSet rsa = s.executeQuery(sql);
                if (rsa.next()) {
                    result = rsa.getInt(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getUnreadMessage");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;

    }

    public void logUserBalance(int userID, int serviceID, int money,
            String description, int add_money, int type, int logGameID) {
        try {
            log.info("Enter logUserBalance");
            // CREATE TABLE newtable LIKE oldtable
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Statement s1 = conn.createStatement();
            Statement s2 = conn.createStatement();
            try {
                if (logDateCurrent != logUserBalanceDateLatest) {
                    // create new tables
                    String searchTable = String.format("SELECT table_name "
                            + "FROM information_schema.tables "
                            + "WHERE table_schema = 'gfsserver' "
                            + "AND table_name = 'log_user_balance_%s'", logDateCurrent);
                    ResultSet rs = s1.executeQuery(searchTable);
                    if (!rs.next()) {
                        String sql = String.format(
                                "CREATE TABLE log_user_balance_%s LIKE log_user_balance;",
                                logDateCurrent);
                        s2.execute(sql);
                        logUserBalanceDateLatest = logDateCurrent;
                    }
                }

                String sql = String.format(
                        "INSERT INTO log_user_balance_%s (log_id, userid, service_id, money, description, difference_amount, type, game_id, log_game_id) "
                        + "VALUES (DEFAULT, %d, %d, %d, '%s', %d, %d, %d, %d)", logDateCurrent, userID, serviceID, money, description, add_money, type, serviceID, logGameID);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                s2.close();
                s1.close();
                conn.close();
                log.info("Exit logUserBalance");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public void updateClientUpdate(int userID, String userName,
            String devicePlatform, String deviceOS, String clientVersion,
            int screenWidth, int screenHeight, String ipAddress) {
        try {
            log.info("Enter updateClientUpdate");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Statement s1 = conn.createStatement();
            Statement s2 = conn.createStatement();

            try {
                if (logDateCurrent != logDateLatest) {
                    // create new tables
                    String searchTable = String.format("SELECT table_name "
                            + "FROM information_schema.tables "
                            + "WHERE table_schema = 'gfsserver' "
                            + "AND table_name = 'log_client_update_%s'", logDateCurrent);
                    ResultSet rs = s1.executeQuery(searchTable);
                    if (!rs.next()) {
                        String sql = String.format(
                                "CREATE TABLE log_client_update_%s LIKE log_client_update;",
                                logDateCurrent);
                        s2.execute(sql);
                        logDateLatest = logDateCurrent;
                    }
                }

                String sql = String.format(
                        "INSERT INTO log_client_update_%s (log_id, userid, username, "
                        + "device_platform, device_os, old_client_version, "
                        + "screen_width, screen_height, ipaddr) "
                        + "VALUES (DEFAULT, %d, '%s', '%s', '%s', '%s', %d, %d, '%s')",
                        logDateCurrent,
                        userID, userName, devicePlatform, deviceOS, clientVersion,
                        screenWidth, screenHeight, ipAddress);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                s1.close();
                s2.close();
                conn.close();
                log.info("Exit updateClientUpdate");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public int logGameTable(String formatStr, String varStr, int bet_money, String first_player, int gameService) {
        int result = 0;
        try {
            log.info("Enter logGameTable");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Statement s1 = conn.createStatement();
            Statement s2 = conn.createStatement();
            try {
                if (logDateCurrent != logGameDateLatest) {
                    // create new tables
                    String searchTable = String.format("SELECT table_name "
                            + "FROM information_schema.tables "
                            + "WHERE table_schema = 'gfsserver' "
                            + "AND table_name = 'log_game_%s'", logDateCurrent);
                    ResultSet rs = s1.executeQuery(searchTable);
                    if (!rs.next()) {
                        String sql = String.format(
                                "CREATE TABLE log_game_%s LIKE log_game;",
                                logDateCurrent);
                        s2.execute(sql);
                        logGameDateLatest = logDateCurrent;
                    }
                }


                String formatSql = "INSERT INTO log_game_" + logDateCurrent
                        + "(log_game_id," + formatStr
                        + "bet_money,first_player, game_id) VALUES"
                        + "(DEFAULT," + varStr
                        + bet_money + ",'" + first_player + "'," + gameService + ")";

                PreparedStatement stmt = conn.prepareStatement(formatSql, Statement.RETURN_GENERATED_KEYS);
                stmt.executeUpdate(formatSql, Statement.RETURN_GENERATED_KEYS);
                ResultSet res = stmt.getGeneratedKeys();
                while (res.next()) {
                    result = res.getInt(1);
                }
                //s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                s1.close();
                s2.close();
                conn.close();
                log.info("Exit logGameTable");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }

        return result;
    }

    public void notifyEventUserLogin(UserObj user) {
        try {
            log.info("Enter notifyEventUserLogin");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format("CALL eventUserLogin(%d);", user.userid);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit notifyEventUserLogin");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public float[] getOnlineTime(int userID) {
        float result[] = new float[2];
        try {
            log.info("Enter getOnlineTime");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Statement s1 = conn.createStatement();
            try {
                ResultSet rs = s.executeQuery("select total_online_time,total_lv_time from user_online_time where userid=" + userID + ";");
                if (rs.next()) {
                    result[0] = rs.getFloat(1);
                    result[1] = rs.getFloat(2);
                } else {
                    String sqlString = String.format("INSERT INTO user_online_time VALUES(%d,'%s',0,0)", userID, VCoreUserDataProvider.getInstance().GetUserNamebyId(userID));
                    s1.execute(sqlString);
                    result[0] = result[1] = 0;
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                s1.close();
                conn.close();
                log.info("Exit getOnlineTime");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public int[] getNumberWonMatch(int userID, String date) {
        int[] result = new int[2];
        int numWonMatch = 0;
        int numTotalMatch = 0;
        try {
            log.info("Enter getNumberWonMatch");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlStatement = String.format(
                        "SELECT COUNT(*), SUM(result) FROM best_player_%s "
                        + "WHERE (game_id=0) AND (userid=%d) ORDER BY userid;", date, userID);
                ResultSet rs = s.executeQuery(sqlStatement);
                if (rs.next()) {
                    numTotalMatch = rs.getInt(1);
                    int rate = rs.getInt(2);
                    numWonMatch = (numTotalMatch + rate) / 2;
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getNumberWonMatch");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        result[0] = numWonMatch;
        result[1] = numTotalMatch;
        return result;
    }

    public int[] getLastStat(int userID) {
        int[] result = new int[2];
        int totalWonMatch = 0;
        int totalGame = 0;
        try {
            log.info("Enter getLastStat");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlStatement = String.format(
                        "SELECT total_won,total_game FROM user_playing_stat WHERE userid=%d;", userID);
                ResultSet rs = s.executeQuery(sqlStatement);
                if (rs.next()) {
                    totalWonMatch = rs.getInt(1);
                    totalGame = rs.getInt(2);
                } else {
                    s.close();
                    s = conn.createStatement();
                    String sqlOldStatement = String.format(
                            "SELECT COUNT(*),SUM(result) FROM best_player "
                            + "WHERE (game_id=0) AND (userid=%d);", userID);
                    ResultSet rs2 = s.executeQuery(sqlOldStatement);
                    if (rs2.next()) {
                        totalGame = rs2.getInt(1);
                        int rate = rs2.getInt(2);
                        totalWonMatch = (totalGame + rate) / 2;
                    }
                    s.close();
                    s = conn.createStatement();
                    String sqlString = String.format("INSERT INTO user_playing_stat(userid,total_won,total_game) VALUES(%d,%d,%d)", userID, totalWonMatch, totalGame);
                    s.execute(sqlString);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                if (!s.isClosed()) {
                    s.close();
                }
                conn.close();
                log.info("Exit getLastStat");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        result[0] = totalWonMatch;
        result[1] = totalGame;
        return result;
    }

    public void updatePlayingStat(int userID, int updateWonMatch, int totalgame) {
        try {
            log.info("Enter updatePlayingStat");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlStatement = String.format(
                        "UPDATE user_playing_stat SET total_won=%d, total_game=%d WHERE userid=%d;", updateWonMatch, totalgame, userID);
                s.execute(sqlStatement);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit updatePlayingStat");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public boolean checkDateLog(String date) {
        boolean result = true;
        try {
            log.info("Enter checkDateLog");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String searchTable = String.format("SELECT table_name "
                        + "FROM information_schema.tables "
                        + "WHERE table_schema = 'gfsserver' "
                        + "AND table_name = 'best_player_%s'", date);
                ResultSet rs = s.executeQuery(searchTable);
                if (!rs.next()) {
                    result = false;
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit checkDateLog");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public void updateLV(int userID, int level, long exp) {
        try {
            log.info("Enter updateLV");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlStatement = String.format("UPDATE users SET user_level=%d,exp=%d WHERE userid=%d;", level, exp, userID);

                s.execute(sqlStatement);

            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit updateLV");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public void updateOnlineTime(int userID, float totalTime, float lvTime) {
        try {
            log.info("Enter updateOnlineTime");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlQuery = "UPDATE user_online_time SET total_online_time =" + totalTime + ",total_lv_time = " + lvTime + "WHERE userid=" + userID;
                s.execute(sqlQuery);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit updateOnlineTime");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    // check if user introduced by someone
    // param: userID - user has been introduced
    //        introByUserID - user introduces
    // - nguoi choi chi duoc gioi thieu 1 lan duy nhat
    // - nguoi gioi thieu duoc nhieu nguoi khac nhau
    //   tren cac so dien thoai khac nhau
    // - nguoi da duoc gioi thieu, ko duoc gioi thieu nguoc lai nguoi gioi thieu
    public boolean checkUserIntroductionPromotion(int introByUserID, int userID) {
        boolean retval = false;
        final String PROMOTION_START_DATE = "'2011-06-30'";
        final String PROMOTION_END_DATE = "'2011-07-31'";
        try {
            log.info("Enter checkUserIntroductionPromotion");
            Connection conn = getConnection();
            Statement s = conn.createStatement();

            try {
                // kiem tra nguoi gioi thieu co ton tai
                String sqlStatement = "SELECT user_id FROM vivoo_center.core_users "
                        + "WHERE user_id=" + introByUserID;
                ResultSet rs = s.executeQuery(sqlStatement);
                retval = rs.next();
                s.close();

                // kiem tra userID da tung duoc gioi thieu chua
                if (retval) {
                    s = conn.createStatement();
                    sqlStatement =
                            "SELECT COUNT(userid) FROM user_intro_promotion "
                            + "WHERE userid=" + userID;
                    rs = s.executeQuery(sqlStatement);
                    if (rs.next()) {
                        retval = rs.getInt(1) == 0;
                    }
                    if (!retval) {
                        log.debug("error promotion: user da tung dc gioi thieu");
                    }
                    s.close();
                } else {
                    log.debug("error promotion: nguoi gioi thieu ko ton tai");
                }

                // kiem tra userID la nguoi moi kich hoat sau ngay bat dau su kien
                if (retval) {
                    retval = false;
                    s = conn.createStatement();
                    sqlStatement = "SELECT COUNT(user_id) FROM vivoo_center.core_users "
                            + "WHERE FROM_UNIXTIME(user_regtime) >= " + PROMOTION_START_DATE
                            + " AND FROM_UNIXTIME(user_regtime) <= " + PROMOTION_END_DATE
                            + " AND user_id=" + userID;
                    rs = s.executeQuery(sqlStatement);
                    if (rs.next()) {
                        retval = rs.getInt(1) == 1;
                    }
                    if (!retval) {
                        log.debug("error promotion: user kich hoat sau ngay bat dau su kien");
                    }
                    s.close();
                }

                // kiem tra gioi thieu cheo ko
                if (retval) {
                    s = conn.createStatement();
                    sqlStatement =
                            "SELECT COUNT(userid) FROM user_intro_promotion "
                            + "WHERE userid=" + introByUserID + " AND intro_by_userid=" + userID;
                    rs = s.executeQuery(sqlStatement);
                    if (rs.next()) {
                        retval = rs.getInt(1) == 0;
                    }
                    if (!retval) {
                        log.debug("error promotion: gioi thieu cheo");
                    }
                    s.close();
                }

                // nguoi moi ko duoc trung so phone voi nhung acc da duoc
                // gioi thieu boi cung nguoi
                if (retval) {
                    s = conn.createStatement();
                    sqlStatement =
                            "SELECT user_id, user_mobile FROM vivoo_center.core_users "
                            + "WHERE user_id=" + userID + " AND user_mobile NOT IN "
                            + "(SELECT DISTINCT user_mobile FROM user_intro_promotion "
                            + "INNER JOIN vivoo_center.core_users ON "
                            + "   core_users.user_id = user_intro_promotion.userid "
                            + "WHERE intro_by_userid=" + introByUserID + ")";

                    rs = s.executeQuery(sqlStatement);
                    retval = rs.next();
                    if (!retval) {
                        log.debug("error promotion: gioi thieu trung so phone");
                    }
                    s.close();
                }

                // kiem tra 2 nick duoc tao tren 1 so phone
                if (retval) {
                    s = conn.createStatement();
                    sqlStatement = "SELECT COUNT(DISTINCT user_mobile) "
                            + "FROM vivoo_center.core_users "
                            + "WHERE user_id IN (" + userID + "," + introByUserID + ")";
                    rs = s.executeQuery(sqlStatement);
                    if (rs.next()) {
                        retval = rs.getInt(1) == 0;
                    }
                    if (!retval) {
                        log.debug("error promotion: 2 nick duoc tao tren 1 so phone");
                    }
                    s.close();
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                if (!s.isClosed()) {
                    s.close();
                }
                conn.close();
                log.info("Exit checkUserIntroductionPromotion");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            retval = false;
        }
        return retval;
    }

    // update list of user introduction
    // param: userID - user has been introduced
    //        introByUserID - user introduces
    public boolean addUserIntroductionPromotion(int introByUserID, int userID) {
        boolean retval = false;
        try {
            log.info("Enter addUserIntroductionPromotion");
            retval = checkUserIntroductionPromotion(introByUserID, userID);

            if (retval) {
                Connection conn = getConnection();

                Statement s = conn.createStatement();
                try {
                    String sqlStatement =
                            "INSERT INTO user_intro_promotion (userid, intro_by_userid) "
                            + "VALUES (" + userID + ", " + introByUserID + ");";
                    s.execute(sqlStatement);
                    retval = true;
                } catch (Exception ex) {
                    log.error(ex.getClass().getName(), ex);
                } finally {
                    s.close();
                    conn.close();
                }
            }
        } catch (Exception e) {
            log.info("Exit addUserIntroductionPromotion");
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return retval;
    }

    public void removeUserIntroductionPromotion(int userID) {
        try {
            log.info("Enter removeUserIntroductionPromotion");
            Connection conn = getConnection();

            Statement s = conn.createStatement();
            try {
                String sqlStatement =
                        "DELETE FROM user_intro_promotion "
                        + "WHERE userid=" + userID;
                s.execute(sqlStatement);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit removeUserIntroductionPromotion");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    //Anlh add : get dynamic client version from database -- 28/06
    public ArrayList getLastestClientVersion() {

        ArrayList<String> clientVer = new ArrayList<String>();
        try {
            log.info("Enter getLastestClientVersion");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Statement s1 = conn.createStatement();
            Statement s2 = conn.createStatement();

            String client_version_required = null;
            String client_deployment_count = null;
            String client_version = null;
            try {
                //get client_version_required
                String sqlStatement = "SELECT property_value FROM global_properties "
                        + "WHERE property_name= 'client_version_required'";
                ResultSet rs = s.executeQuery(sqlStatement);
                if (rs.next()) {
                    client_version_required = rs.getString("property_value");
                }

                //get client_version
                sqlStatement = "SELECT property_value FROM global_properties "
                        + "WHERE property_name= 'client_version'";
                rs = s1.executeQuery(sqlStatement);
                if (rs.next()) {
                    client_version = rs.getString("property_value");
                }

                //get client_deployment_count
                sqlStatement = "SELECT property_value FROM global_properties "
                        + "WHERE property_name= 'client_deployment_count'";
                rs = s2.executeQuery(sqlStatement);
                if (rs.next()) {
                    client_deployment_count = rs.getString("property_value");
                }
                clientVer.add(0, client_version_required);
                clientVer.add(1, client_version);
                clientVer.add(2, client_deployment_count);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                s1.close();
                s2.close();
                conn.close();
                log.info("Exit getLastestClientVersion");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return clientVer;
    }

    public Date getLastLoginDate(int userID) {
        Date result = null;
        try {
            log.info("Enter getLastLoginDate");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                ResultSet rsa = s.executeQuery("select last_login from users where userid=" + userID);
                if (rsa.next()) {
                    result = rsa.getDate(1);
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getLastLoginDate");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return result;
    }

    public ArrayList getEventConfig() {

        ArrayList<String> eventConfig = new ArrayList<String>();
        try {
            log.info("Enter getEventConfig");
            Connection conn = getConnection();
            Statement s = conn.createStatement();

            String start, end, small, big, smallgift, biggift, type;
            start = end = small = big = smallgift = biggift = type = "";

            try {
                //get client_version_required
                String sqlStatement = "SELECT property_name,property_value FROM global_properties;";
//                        + "WHERE property_name= 'client_version_required'";
                ResultSet rs = s.executeQuery(sqlStatement);
                while (rs.next()) {
                    String prop_name = rs.getString("property_name");
                    if (rs.getString(1) == null || rs.getString(2) == null) {
                        continue;
                    }
                    if (prop_name.equals("event_start")) {
                        start = rs.getString("property_value");
                    }
                    if (prop_name.equals("event_end")) {
                        end = rs.getString("property_value");
                    }
                    if (prop_name.equals("low_standard")) {
                        small = rs.getString("property_value");
                    }
                    if (prop_name.equals("high_standard")) {
                        big = rs.getString("property_value");
                    }
                    if (prop_name.equals("low_prize")) {
                        smallgift = rs.getString("property_value");
                    }
                    if (prop_name.equals("high_prize")) {
                        biggift = rs.getString("property_value");
                    }
                    if (prop_name.equals("event_type")) {
                        type = rs.getString("property_value");
                    }
                }

                //get client_version
//                sqlStatement = "SELECT property_value FROM global_properties "
//                        + "WHERE property_name= 'client_version'";
//                rs = s1.executeQuery(sqlStatement);
//                if (rs.next()) {
//
//                    client_version = rs.getString("property_value");
//                }
//
//                //get client_deployment_count
//                sqlStatement = "SELECT property_value FROM global_properties "
//                        + "WHERE property_name= 'client_deployment_count'";
//                rs = s2.executeQuery(sqlStatement);
//                if (rs.next()) {
//                    client_deployment_count = rs.getString("property_value");
//                }
                eventConfig.add(0, start);
                eventConfig.add(1, end);
                eventConfig.add(2, small);
                eventConfig.add(3, big);
                eventConfig.add(4, smallgift);
                eventConfig.add(5, biggift);
                eventConfig.add(6, type);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getEventConfig");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return eventConfig;
    }

    public CharacterObj loadCharacterData(int userID) {
        CharacterObj charObj = null;
        try {
            String key = "CHAR" + String.valueOf(userID);
            if (Memcache.getInstance().get(key) != null) {
                charObj = (CharacterObj) Memcache.getInstance().get(key);
                return charObj;
            }

            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("SELECT "
                        + "characters.userid, "
                        + "characters.char_level, "
                        + "characters.char_sex, "
                        + "characters.char_hair, "
                        + "characters.char_head, "
                        + "characters.char_eyes, "
                        + "characters.char_emotion, "
                        + "characters.char_face_item, "
                        + "characters.char_hand_item, "
                        + "characters.char_head_item, "
                        + "characters.char_shirt, "
                        + "characters.char_pant, "
                        + "characters.petid, "
                        + "characters.index_friendly, "
                        + "characters.index_hostility, "
                        + "characters.index_stylish, "
                        + "characters.index_happy, "
                        + "characters.index_healthy, "
                        + "pet.petid, "
                        + "pet.pet_type, "
                        + "pet.pet_health_index "
                        + "FROM PIDB.characters "
                        + "INNER JOIN PIDB.pet ON characters.petid = pet.petid "
                        + "WHERE characters.userid = %d", userID);
                ResultSet rs = s.executeQuery(strSQL);

                if (rs.next()) {
                    charObj = new CharacterObj();
                    charObj.userid = rs.getInt("userid");
                    charObj.level = rs.getInt("char_level");
                    charObj.sex = (byte) rs.getInt("char_sex");
                    charObj.hair = (byte) rs.getInt("char_hair");
                    charObj.head = (byte) rs.getInt("char_head");
                    charObj.eyes = (byte) rs.getInt("char_eyes");
                    charObj.emotion = (byte) rs.getInt("char_emotion");
                    charObj.faceItem = (byte) rs.getInt("char_face_item");
                    charObj.handItem = (byte) rs.getInt("char_hand_item");
                    charObj.headItem = (byte) rs.getInt("char_head_item");
                    charObj.shirt = (byte) rs.getInt("char_shirt");
                    charObj.pant = (byte) rs.getInt("char_pant");
                    charObj.idxFriendly = (byte) rs.getInt("index_friendly");
                    charObj.idxHostility = (byte) rs.getInt("index_hostility");
                    charObj.idxStylish = (byte) rs.getInt("index_stylish");
                    charObj.idxHappy = (byte) rs.getInt("index_happy");
                    charObj.idxHealthy = (byte) rs.getInt("index_healthy");
                    charObj.petid = rs.getInt("petid");
                    charObj.petType = (byte) rs.getInt("pet_type");
                    charObj.petHealth = (byte) rs.getInt("pet_health_index");
                }
            } catch (Exception ex) {
                ex.printStackTrace();
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return charObj;
    }

    public byte createCharacter(int userID, int gender, int hair, int head, int eyes, int shirt, int pant) {
        byte result = 1;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "INSERT INTO PIDB.characters VALUES (%d, 1, %d, %d, %d, %d, 1, %d, %d, 0, 0, 0, 0"
                        + " , 50, 0, 0, 50, 50)", userID, gender, hair, head, eyes, shirt, pant);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit createCharacter");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    public byte updateCharacterItem(int userID, int category, int itemID) {
        byte result = 1;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strUserItem = null;
                switch (category) {
                    case PIConstants.ITEM_CATEGORY_ON_FACE:
                        strUserItem = "char_face_item";
                        break;

                    case PIConstants.ITEM_CATEGORY_ON_HEAD:
                        strUserItem = "char_head_item";
                        break;

                    case PIConstants.ITEM_CATEGORY_IN_HAND:
                        strUserItem = "char_hand_item";
                        break;

                    case PIConstants.ITEM_CATEGORY_SHIRT:
                        strUserItem = "char_shirt";
                        break;

                    case PIConstants.ITEM_CATEGORY_PANT:
                        strUserItem = "char_pant";
                        break;
                }

                String sql = String.format(
                        "UPDATE PIDB.characters SET %s=%d "
                        + "WHERE userid=%d", strUserItem, itemID, userID);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit updateCharacterItem");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    public byte transferItem(int fromUserID, int toUserID, int itemID) {
        log.info("transfer item");
        byte result = 1;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sql = String.format(
                        "UPDATE PIDB.user_inventory SET userid=%d "
                        + "WHERE item_id=%d AND userid=%d", toUserID, itemID, fromUserID);
                s.execute(sql);
                VCoreUserDataProvider.getInstance().AddItem(toUserID, "add item", itemID);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit transferItem");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    public byte updateUserIndex(int userID, int index, int dif) {
        log.info("update User Index");
        byte result = 1;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            CharacterObj charObj = loadCharacterData(userID);
            try {
                String strUserIndex = null;
                int newIndex = 0;
                switch (index) {
                    case PIConstants.USER_INDEX_FRIENDLY:
                        strUserIndex = "index_friendly";
                        newIndex = charObj.idxFriendly + dif;
                        break;

                    case PIConstants.USER_INDEX_HAPPY:
                        strUserIndex = "index_happy";
                        newIndex = charObj.idxHappy + dif;
                        break;

                    case PIConstants.USER_INDEX_HEALTHY:
                        strUserIndex = "index_healthy";
                        newIndex = charObj.idxHealthy + dif;
                        break;

                    case PIConstants.USER_INDEX_HOSTILITY:
                        strUserIndex = "index_hostility";
                        newIndex = charObj.idxHostility + dif;
                        break;

                    case PIConstants.USER_INDEX_STYLISH:
                        strUserIndex = "index_stylish";
                        newIndex = charObj.idxStylish + dif;
                        break;
                }

                newIndex = Math.min(Math.max(0, newIndex), 100);

                String sql = String.format(
                        "UPDATE PIDB.characters SET %s=%d "
                        + "WHERE userid=%d", strUserIndex, newIndex, userID);
                s.execute(sql);
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
                result = 0;
            } finally {
                s.close();
                conn.close();
                log.info("Exit transferItem");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            result = 0;
        }
        return result;
    }

    /*
     * HaNH
     */
    /*
     * Fishing
     */
    public ArrayList<ItemObj> getFishingItemsFromUserInventory(int userid, int category) {
        ArrayList<ItemObj> result = new ArrayList<ItemObj>();
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            Date date;
            String sql = String.format(
                    "SELECT "
                    + "user_inventory.inventory_id, "
                    + "user_inventory.item_id, "
                    + "user_inventory.creation_date, "
                    + "user_inventory.expired_date, "
                    + "user_inventory.usages, "
                    + "user_inventory.status, "
                    + "items.item_category, "
                    + "items.name, "
                    + "items.level_required, "
                    + "fishing_items.bait_time, "
                    + "fishing_items.use_for_fish "
                    + "FROM "
                    + "PIDB.user_inventory "
                    + "INNER JOIN PIDB.items ON items.item_id = user_inventory.item_id "
                    + "INNER JOIN PIDB.fishing_items ON fishing_items.item_id = user_inventory.item_id "
                    + "WHERE "
                    + "(user_inventory.user_id = %d) AND items.item_category=%d "
                    + "AND ((user_inventory.expired_date > NOW()) OR (ISNULL(user_inventory.expired_date))) "
                    + "ORDER BY inventory_id DESC",
                    userid, category);            
            try {
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    ItemObj item = new ItemObj();
                    item.id = rs.getInt("item_id");
                    item.inventory_id = rs.getInt("inventory_id");
                    item.name = rs.getString("name");
                    item.category = rs.getInt("item_category");
                    date = rs.getDate("creation_date");
                    item.creation_date = CommonUtils.ParseSQLDate(date);
                    date = rs.getDate("expired_date");
                    item.expired_date = CommonUtils.ParseSQLDate(date);
                    item.usages = rs.getInt("usages");
                    item.status = rs.getInt("status");
                    item.level = rs.getInt("level_required");
                    item.timecatch = rs.getInt("bait_time");
                    item.useforfish = rs.getByte("use_for_fish");
                    result.add(item);
                }
            } catch (Exception e) {
                log.error(e.getClass().getName(), e);
                e.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit transferItem");
            }
        } catch (Exception e) {
            log.error(e.getClass().getName(), e);
            e.printStackTrace();
        }


        return result;
    }

    public ArrayList<ItemObj> getFishingItems(int userid, byte mapid, int category) {
        ArrayList<ItemObj> result = new ArrayList<ItemObj>();
        ArrayList<ItemObj> items = getFishingItemsFromUserInventory(userid, category);
        for (int i = 0; i < items.size(); i++) {
            ItemObj item = items.get(i);
            if ((byte) (item.useforfish & mapid) != 0) {
                if ((category == PIConstants.ITEM_CATEGORY_BAIT && item.usages > 0) || category != PIConstants.ITEM_CATEGORY_BAIT) {
                    ItemObj temp_item = checkItemExist(result, item.id);
                    if (temp_item == null) {
                        result.add(item);
                    } else {
                        temp_item.usages += item.usages;
                    }
                }
            }
        }
        return result;
    }

    private ItemObj checkItemExist(ArrayList<ItemObj> items, int itemid) {

        for (int i = 0; i < items.size(); i++) {
            ItemObj item = items.get(i);
            if (item.id == itemid) {
                return item;
            }
        }
        return null;
    }

    public boolean useFishingBait(int userid, byte mapid, int bait) {
        boolean result = false;
        ArrayList<ItemObj> items = getFishingItemsFromUserInventory(userid, PIConstants.ITEM_CATEGORY_BAIT);
        for (int i = 0; i < items.size(); i++) {
            ItemObj item = items.get(i);
            if (((byte) (item.useforfish & mapid) != 0) && bait == item.id && item.usages > 0) {
                result = true;

                updateUserItemUsages(userid, item.inventory_id, item.usages - 1);
                i = items.size(); // Skip Loop
            }
        }
        return result;
    }

    public int getFishID(byte map) {
        int result = 0;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            String sql = String.format(
                    "SELECT fishing_items.item_id,use_for_fish  "
                    + "FROM PIDB.items "
                    + "INNER JOIN PIDB.fishing_items ON fishing_items.item_id=items.item_id "
                    + "WHERE items.item_category=%d", PIConstants.ITEM_CATEGORY_FISH);            
            try {
                ResultSet rs = s.executeQuery(sql);
                while (rs.next()) {
                    byte usefor = rs.getByte("use_for_fish");
                    if (((byte) usefor & map) == map) {
                        result = rs.getInt("item_id");
                    }
                }
            } catch (Exception e) {
                log.error("Get Statement error : ", e.getClass().getName(), e);
                e.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit transferItem");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return result;
    }

    /*
     * Farm
     */
    public int getFarmMoney(int userid) {
        int balance = 0;
        try {
            log.info("Enter getFarmMoney");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            String strSQL = String.format("SELECT farm_money FROM PIDB.farm_users WHERE user_id=%d", userid);
            try {
                ResultSet rs = s.executeQuery(strSQL);
                rs.next();
                balance = rs.getInt("farm_money");
            } catch (Exception e) {
                log.error(e.getClass().getName(), e);
                e.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit transferItem");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return balance;
    }

    public ArrayList getFieldList(int userid) {
        ArrayList result = new ArrayList();
        try {
            log.info("Enter getFieldList");
            Connection conn = getConnection();
            Statement s = conn.createStatement();

            try {
                String strSQL = String.format("SELECT land_id,base_sanluong,"
                        + "farm_land.item_id,san_luong,item_name,was_fertilizing,"
                        + "TIMESTAMPDIFF(MINUTE,time_plant,NOW()) as timepass,"
                        + "duration,base_duration,"
                        + "TIMESTAMPDIFF(MINUTE,time_grass1,NOW())as grass1,"
                        + "TIMESTAMPDIFF(MINUTE,time_grass2,NOW())as grass2,"
                        + "TIMESTAMPDIFF(MINUTE,time_worm,NOW())as worm,"
                        + "TIMESTAMPDIFF(MINUTE,time_dry,NOW())as dry, "
                        + "is_grown, is_death "
                        + "FROM PIDB.farm_land "
                        + "WHERE PIDB.farm_land.user_id = %d", userid);


                ResultSet rs = s.executeQuery(strSQL);
                while (rs.next()) {

                    FarmLand land = new FarmLand();
                    land.landId = rs.getInt("land_id");
                    land.itemname = rs.getString("item_name");
                    land.itemId = rs.getInt("item_id");

                    int base_duration = rs.getInt("base_duration");
                    land.duration = rs.getInt("duration");
                    int timegrass1 = rs.getInt("grass1");
                    int timegrass2 = rs.getInt("grass2");
                    int timeworm = rs.getInt("worm");
                    int timedry = rs.getInt("dry");
                    int timepass = rs.getInt("timepass");
                    int sanluong = rs.getInt("san_luong");
                    int base_sanluong = rs.getInt("base_sanluong");






                    /*
                     * Kiem Tra da thu hoach duoc chua
                     */



                    int isgrown = rs.getInt("is_grown");
                    int isdeath = rs.getInt("is_death");

                    if (land.itemId == 0) {// Do nothing
//                        if (timegrass1 > 0) {  // Grass fist time occur . 25%
//                            land.disease |= PIConstants.VALUE_BYTE_TREE_GRASS1;
//                        }
//                        if (timegrass2 > 0) {  // Grass second time occur
//                            land.disease |= PIConstants.VALUE_BYTE_TREE_GRASS2;
//                        }
//                        if (timeworm > 0) {
//                            land.disease |= PIConstants.VALUE_BYTE_TREE_WORM;
//                        }
                        if (timedry > 0) {
                            land.disease |= PIConstants.VALUE_BYTE_TREE_DRY;
                        }
                    } else {


                        if (isdeath == 1) { // Cay Heo
                            land.durationleft = -2;
                        } else if (isdeath == 2) { // Cay da duoc thu hoach
                            land.durationleft = -3;
                        } else if (isgrown == 1) {
                            land.durationleft = -1;
                        } else {
                            if (land.duration < timepass) {  // Da thu hoach duoc
                                if (timepass - land.duration > 2 * 60) {  // Lon hon 2 tieng, bi heo, tinh bang phut
                                    land.durationleft = -2;
                                    isdeath = 1;
                                    isgrown = 1;
                                } else {
                                    land.durationleft = -1;
                                    isdeath = 0;
                                    isgrown = 1;
                                }

                                strSQL = String.format("UPDATE PIDB.farm_land SET is_grown=%d,is_death=%d, san_luong=%d WHERE PIDB.farm_land.user_id = %d AND PIDB.farm_land.land_id=%d ", isgrown, isdeath, sanluong, userid, land.landId);
                                query(strSQL);

                            } else {
                                land.durationleft = land.duration - timepass;
                                if (rs.getInt("was_fertilizing") == 0) {
                                    land.disease |= PIConstants.VALUE_BYTE_WAS_NOT_FERTILIZING;
                                }
                            }
                        }

                        if (land.durationleft > 0 || land.durationleft == -1) {
                            if (timegrass1 > 0) {  // Grass fist time occur . 25%
                                land.duration += base_duration / 4;
                                land.disease |= PIConstants.VALUE_BYTE_TREE_GRASS1;

                            }
                            if (timegrass2 > 0) {  // Grass second time occur
                                land.duration += base_duration / 4;
                                land.disease |= PIConstants.VALUE_BYTE_TREE_GRASS2;
                            }
                            if (timeworm > 0) {
                                land.disease |= PIConstants.VALUE_BYTE_TREE_WORM;
                                sanluong = sanluong - base_sanluong / 4;
                            }
                            if (timedry > 0) {
                                land.disease |= PIConstants.VALUE_BYTE_TREE_DRY;
                                sanluong = sanluong - base_sanluong / 5;
                            }
                        }
                    }

                    result.add(land);
                }
            } catch (Exception e) {
                log.error("Get Statement error : ", e.getClass().getName(), e);
                e.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit GetFieldList");
            }

        } catch (Exception ex) {
            log.error("Get Statement error : ", ex.getClass().getName(), ex);
            ex.printStackTrace();
        }
        return result;
    }

    private void query(String sql) {
        try {
            log.info("Enter query");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                s.execute(sql);
            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit DoQuery");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
    }

    public ArrayList getFarmAnimnals(int userid) {
        log.info("Enter getFarmAnimnals");
        updateAnimalProduct(userid);
        ArrayList result = new ArrayList();
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String strSQL = String.format("SELECT id,item_name,category,"
                        + "farm_animals.item_id,"
                        + "TIMESTAMPDIFF(MINUTE,time_born,farm_animals.time_grow) as grow,"
                        + "TIMESTAMPDIFF(MINUTE,NOW(),farm_animals.time_grow) as growleft,"
                        + "TIMESTAMPDIFF(MINUTE,time_death,NOW())as death,"
                        + "TIMESTAMPDIFF(MINUTE,time_tired,NOW())as tired,"
                        + "TIMESTAMPDIFF(MINUTE,time_hungry,NOW())as hungry,"
                        + "TIMESTAMPDIFF(MINUTE,time_have_flu,NOW())as haveflu,"
                        + "TIMESTAMPDIFF(MINUTE,time_headache,NOW())as headache,"
                        + "TIMESTAMPDIFF(MINUTE,last_time_collect,NOW()) as produce "
                        + "FROM PIDB.farm_animals "
                        + "INNER JOIN PIDB.farm_items ON farm_items.item_id=farm_animals.item_id "
                        + "WHERE PIDB.farm_animals.user_id = %d", userid);

                ResultSet rs = s.executeQuery(strSQL);
                while (rs.next()) {
                    FarmAnimals animals = new FarmAnimals();
                    animals.id = rs.getInt("id");
                    animals.itemid = rs.getInt("item_id");
                    animals.timegrow = rs.getInt("grow");
                    animals.timegrowleft = rs.getInt("growleft");
                    animals.itemname = rs.getString("item_name");
                    animals.category=rs.getInt("category");
                    int death = rs.getInt("death");
                    int produce = rs.getInt("produce");
                    if (death >= 0) { // Death
                        animals.timegrowleft = -2;
                        animals.produce = produce;
                    } else { // Alive
                        if (animals.timegrowleft <= 0) {
                            animals.timegrowleft = -1; // Truong Thanh
                        }

                        if (rs.getInt("hungry") >= 0) {
                            animals.disease |= PIConstants.VALUE_BYTE_ANIMAL_HUNGRY;
                        }
                        if (rs.getInt("tired") >= 0) {
                            animals.disease |= PIConstants.VALUE_BYTE_ANIMAL_TIRED;
                        }
                        if (rs.getInt("haveflu") >= 0) {
                            animals.disease |= PIConstants.VALUE_BYTE_ANIMAL_HAVE_FLU;
                        }
                        if (rs.getInt("headache") >= 0) {
                            animals.disease |= PIConstants.VALUE_BYTE_ANIMAL_HEADACHE;
                        }

                        if (animals.timegrowleft == -1 && produce > 0) { // Truong thanh, Lay Trung + Sua

                            animals.produce = produce;
                        }

                    }
                    result.add(animals);
                }

            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Get Animals");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

        return result;
    }

    public void transferFarmATM(int userid, int amountSend, int amountReceive, boolean isFromMain) {
        try {

            log.info("Enter transferFarmATM");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sendQuery;
                String receiveQuery;
                if (isFromMain) {
                    sendQuery = String.format("UPDATE users SET money=money-%d WHERE userid=%d", amountSend, userid);

                    receiveQuery = String.format("UPDATE PIDB.farm_users SET farm_money=farm_money+%d WHERE user_id=%d", amountReceive, userid);
                } else {
                    sendQuery = String.format("UPDATE users SET money=money+%d WHERE userid=%d", amountReceive, userid);
                    receiveQuery = String.format("UPDATE PIDB.farm_users SET farm_money=farm_money-%d WHERE user_id=%d", amountSend, userid);

                }


                s.addBatch(sendQuery);
                s.addBatch(receiveQuery);
                s.executeBatch();
            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Transfer ATM");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
    }

    public int countItemFarmStoreByCategory(int userid, int category, int type) {
        int count = 0;
        try {
            log.info("Enter countItemFarmStore");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlQuery = String.format("SELECT amount FROM PIDB.farm_store,PIDB.farm_items WHERE user_id=%d AND farm_items.category=%d AND type=%d", userid, category, type);
                ResultSet rs = s.executeQuery(sqlQuery);
                if (rs.next()) {
                    count = rs.getInt("amount");
                }
            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit CountItemFarmStore");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return count;
    }

    public int countItemFarmStoreByID(int userid, int itemid, int type) {
        int count = 0;
        try {
            log.info("Enter countItemFarmStore");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String sqlQuery = String.format("SELECT amount FROM PIDB.farm_store WHERE user_id=%d AND farm_store.item_id=%d AND type=%d", userid, itemid, type);
                ResultSet rs = s.executeQuery(sqlQuery);
                if (rs.next()) {
                    count = rs.getInt("amount");
                }
            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit CountItemFarmStore");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

        return count;
    }

    /*
     * DK de Trong Cay
     * 1. Tren mieng dat khong co cay nao
     * 2.Dat khong co sau, khong co co, khong thieu nuoc
     */
    public boolean canPlantTree(int userid, int landid) {
        boolean result = false;

        try {
            log.info("Enter canPlantTree");
            Connection conn = getConnection();
            Statement s = conn.createStatement();

            try {
                String sqlQuery = String.format("SELECT item_id,"
                        + "TIMESTAMPDIFF(MINUTE,time_grass1,now()) as grass1,"
                        + "TIMESTAMPDIFF(MINUTE,time_grass2,now()) as grass2,"
                        + "TIMESTAMPDIFF(MINUTE,time_worm,now()) as worm,"
                        + "TIMESTAMPDIFF(MINUTE,time_dry,now()) as dry "
                        + "FROM PIDB.farm_land WHERE user_id=%d AND land_id=%d ", userid, landid);
                ResultSet rs = s.executeQuery(sqlQuery);
                if (rs.next()) {
                    int itemid = rs.getInt("item_id");
                    int grass1 = rs.getInt("grass1");
                    int grass2 = rs.getInt("grass2");
                    int worm = rs.getInt("worm");
                    int dry = rs.getInt("dry");

                    if (itemid > 0 || grass1 > 0 || grass2 > 0 || worm > 0 || dry > 0) {
                        result = false;
                    } else {
                        result = true;
                    }
                }


            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit canPlantTree");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return result;
    }

    /*
     * Plant Tree
     * 1. Clear Land
     * 2. Sub Amount of ItemID
     * 3. Dat gio cac su kien
     *
     */
    public void plantTree(int userid, int landid, int itemid) {

        try {

            log.info("Enter plantTree");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                /*
                 * TODO:
                 * timelive lay o dau ra, tinh bang phut
                 */
                String sqlQuery = String.format("SELECT item_name,time_grow,product,level FROM  PIDB.farm_items WHERE item_id=%d ", itemid);
                ResultSet rs = s.executeQuery(sqlQuery);
                rs.next();

                int timelive = rs.getInt("time_grow"); // in Hour
                int product = rs.getInt("product");
                String itemname = rs.getString("item_name");
                int level = rs.getInt("level");

                timelive = timelive * 60; // in Minute

                int timegrass1 = 30 + CommonUtils.random(timelive / 2);
                int timegrass2 = timelive / 2 + CommonUtils.random(timelive / 2 - 15);

                int timeworm = timelive / 4 + CommonUtils.random(timelive / 2);
                int timedry = 30;  // After 30 minutes, the Land is dry

                sqlQuery = String.format("UPDATE PIDB.farm_land SET item_id=%d,item_name='%s',level=%d,"
                        + "time_plant=now(),"
                        + "time_grass1=DATE_ADD(NOW(), INTERVAL %d MINUTE),"
                        + "time_grass2=DATE_ADD(NOW(), INTERVAL %d MINUTE),"
                        + "time_worm=DATE_ADD(NOW(), INTERVAL %d MINUTE),"
                        + "time_dry=DATE_ADD(NOW(), INTERVAL %d MINUTE),"
                        + "base_sanluong=%d, san_luong=%d,base_duration=%d,duration=%d,is_grown=0,is_death=0,stolen=0,"
                        + "dry_percent=0,was_fertilizing=0  "
                        + "WHERE user_id=%d AND land_id=%d ", itemid, itemname, level, timegrass1, timegrass2, timeworm, timedry, product, product, timelive, timelive, userid, landid);

                s.execute(sqlQuery);
                if (s.getUpdateCount() == 1) {
                    useFarmStoreItem(userid, itemid, 1, PIConstants.TYPE_FARM_PLANT);
                }

            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit PlantTree");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
    }

    private int getIDbyTypeCategory(int type, int category) {
        int result = 0;
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            String sql = String.format("SELECT item_id FROM PIDB.farm_items WHERE "
                    + "type=%d AND category=%d", type, category);
            try {
                ResultSet rs = s.executeQuery(sql);
                if (rs.next()) {
                    return rs.getInt("item_id");
                }
            } catch (Exception e) {
                log.error("Get Statement error : ", e.getClass().getName(), e);
                e.printStackTrace();
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return result;
    }

    public int harvest(int userid, int landId) {
        int result = 0;
        try {
            log.info("Enter harvest");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("SELECT "
                        + "item_id, san_luong,stolen,level "
                        + "FROM PIDB.farm_land "
                        + "WHERE PIDB.farm_land.user_id = %d AND land_id=%d AND is_grown=1 AND is_death=0", userid, landId);

                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    int sanluong = rs.getInt("san_luong");
                    int stolen = rs.getInt("stolen");
                    int itemid = rs.getInt("item_id");
                    int level = rs.getInt("level");

                    sanluong = sanluong - stolen;
                    if (sanluong < 0) {
                        sanluong = 0;
                    }

                    int productId = getIDbyTypeCategory(PIConstants.TYPE_FARM_PLANT_PRODUCT, itemid);
                    insertFarmStore(userid, productId, sanluong, PIConstants.TYPE_FARM_PLANT_PRODUCT);

                    strSQL = String.format("UPDATE  PIDB.farm_land SET is_death=2 "
                            + "WHERE user_id=%d  AND land_id=%d", userid, landId);
                    query(strSQL);
                    doIncreaseEx(userid, sanluong * level);
                    return sanluong;
                }


            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit harvest");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return 0;
    }

    public void insertFarmStore(int userid, int itemId, int amount, int type) {

        try {
            log.info("Enter insertFarmStore");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String strSQL = String.format("SELECT * FROM PIDB.farm_store "
                        + "WHERE user_id=%d AND item_id=%d AND type=%d", userid, itemId, type);

                ResultSet rs = s.executeQuery(strSQL);


                if (rs.next()) {
                    strSQL = String.format("UPDATE PIDB.farm_store SET amount=amount+%d WHERE user_id=%d AND item_id=%d AND type=%d", amount, userid, itemId, type);
                } else if (amount > 0) {
                    strSQL = String.format("INSERT INTO PIDB.farm_store SET amount=%d ,user_id=%d , item_id=%d ,type=%d", amount, userid, itemId, type);
                }
                s.execute(strSQL);
            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Insert Farm Store");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

    }

    public void useFarmStoreItem(int userid, int itemId, int amount, int type) {

        try {
            log.info("Enter insertFarmStore");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("UPDATE PIDB.farm_store SET amount=amount-%d WHERE user_id=%d "
                        + "AND type=%d"
                        + "AND item_id=%d",
                        amount, userid, type, itemId);
                s.execute(strSQL);
                strSQL = String.format("DELETE FROM PIDB.farm_store WHERE amount<=0");
                s.execute(strSQL);

            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Insert Farm Store");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

    }

    public int fertilizing(int userid, int landId) {
        int itemCategory = PIConstants.CATEGORY_FARM_FERTILIZE;
        if (countItemFarmStoreByCategory(userid, itemCategory, PIConstants.TYPE_FARM_ITEM_FOR_TREE) == 0) {
            return 0;
        }
        int result = 0;
        try {
            log.info("Enter fertilizing");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                // Check if user has Phan Bon
                //TODO: item cua Phan bon
                // Bon phan

                String strSQL = String.format("UPDATE PIDB.farm_land SET san_luong=san_luong+(base_sanluong/5),"
                        + "duration=duration-(base_duration/5), was_fertilizing=1 "
                        + "WHERE PIDB.farm_land.user_id = %d AND land_id=%d AND was_fertilizing=0 AND is_grown=0 AND item_id!=0", userid, landId);

                s.execute(strSQL);
                if (s.getUpdateCount() == 1) {
                    int itemid = getIDbyTypeCategory(PIConstants.TYPE_FARM_ITEM_FOR_TREE, itemCategory);
                    useFarmStoreItem(userid, itemid, 1, PIConstants.TYPE_FARM_ITEM_FOR_TREE);
                    doIncreaseEx(userid, 2);
                    result = 1;
                } else {
                    result = 0;
                }


            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Insert Fertilizing");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

        return result;
    }

    public int herbicide(int userid, int landId) {
        int itemCategory = PIConstants.CATEGORY_FARM_HERBICIDE;
        if (countItemFarmStoreByCategory(userid, itemCategory, PIConstants.TYPE_FARM_ITEM_FOR_TREE) == 0) {
            return -1; // không có thuốc diệt cỏ
        }
        int result = 0;

        try {
            log.info("Enter herbicide");

            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                // diệt cỏ

                String strSQL = String.format("SELECT land_id,is_grown,"
                        + " TIMESTAMPDIFF(MINUTE,time_grass1,now())as grass1,"
                        + " TIMESTAMPDIFF(MINUTE,time_grass2,now())as grass2 "
                        + "FROM PIDB.farm_land "
                        + "WHERE PIDB.farm_land.user_id = %d AND land_id=%d", userid, landId);

                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    int grass1 = rs.getInt("grass1");
                    int grass2 = rs.getInt("grass2");
                    int isgrown = rs.getInt("is_grown");
                    if (grass1 == 0 && grass2 == 0) { // No GRASS
                        return 0;
                    }
                    String grass = "";
                    int percent = 0;
                    if (grass2 > 0) {
                        grass = "time_grass2";
                        if (grass2 > 15) {
                            percent = 10;
                        } else {
                            percent = 0;
                        }
                    }
                    if (grass1 > 0) {
                        grass = "time_grass1";
                        if (grass1 > 15) {
                            percent = 10;
                        } else {
                            percent = 0;
                        }
                    }

                    if (grass.equals("")) {
                        result = 0;
                    } else {
                        if (isgrown == 1) {  // If
                            percent = 0;
                        }
                        strSQL = String.format("UPDATE PIDB.farm_land SET %s=DATE_ADD(NOW(), INTERVAL 100 YEAR),"
                                + "duration=duration+base_duration*%d/100 "
                                + "WHERE PIDB.farm_land.user_id = %d AND land_id=%d", grass, percent, userid, landId);
                        s.execute(strSQL);
                        if (s.getUpdateCount() > 0) {
                            doIncreaseEx(userid, 2);
                            int itemid = getIDbyTypeCategory(PIConstants.TYPE_FARM_ITEM_FOR_TREE, itemCategory);
                            useFarmStoreItem(userid, itemid, 1, PIConstants.TYPE_FARM_ITEM_FOR_TREE);
                        }
                        result = 1;
                    }
                }

            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit herbicide");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

        return result;

    }

    public int killWorms(int userid, int landId) {

        // diệt sâu

        int itemCategory = PIConstants.CATEGORY_FARM_WORM_DRUG;
        if (countItemFarmStoreByCategory(userid, itemCategory, PIConstants.TYPE_FARM_ITEM_FOR_TREE) == 0) {
            return -1; // không có thuốc sâu
        }
        int result = 0;
        try {
            log.info("Enter killWorms");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("SELECT land_id,is_grown,"
                        + "TIMESTAMPDIFF(MINUTE,time_worm,now()) as worm "
                        + "FROM PIDB.farm_land "
                        + "WHERE PIDB.farm_land.user_id = %d AND land_id=%d", userid, landId);

                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    int worm = rs.getInt("worm");
                    int isgrown = rs.getInt("is_grown");
                    if (worm > 0) {
                        int percent = 0;
                        if (worm > 15) { //
                            percent = 10;
                        }

                        if (isgrown == 1) {
                            percent = 0;
                        }

                        strSQL = String.format("UPDATE PIDB.farm_land SET "
                                + "time_worm=DATE_ADD(NOW(), INTERVAL 100 YEAR),san_luong=san_luong-(base_sanluong*%d/100) "
                                + "WHERE user_id=%d AND land_id=%d", percent, userid, landId);

                        s.execute(strSQL);
                        if (s.getUpdateCount() > 0) {
                            doIncreaseEx(userid, 2);
                            int itemid = getIDbyTypeCategory(PIConstants.TYPE_FARM_ITEM_FOR_TREE, itemCategory);
                            useFarmStoreItem(userid, itemid, -1, PIConstants.TYPE_FARM_ITEM_FOR_TREE);
                        }
                        result = 1;
                    }
                }

            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit KillWorms");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return result;
    }

    public int doSoil(int userid, int landId) {
        int result = 0;
        try {

            log.info("Enter doSoil");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                // làm đất

                String strSQL = String.format("UPDATE PIDB.farm_land SET "
                        + "time_worm=DATE_ADD(NOW(), INTERVAL 300 MINUTE),"
                        + "time_grass1=DATE_ADD(NOW(), INTERVAL 30 MINUTE),"
                        + "time_grass2=DATE_ADD(NOW(), INTERVAL 30 MINUTE),"
                        + "time_dry=DATE_ADD(NOW(), INTERVAL 30 MINUTE),"
                        + "item_id=0,"
                        + "is_grown=0,"
                        + "is_death=0,"
                        + "dry_percent=0,"
                        + "was_fertilizing=0 "
                        + "WHERE user_id=%d AND land_id=%d ", userid, landId);

                s.execute(strSQL);
                if (s.getUpdateCount() > 0) {
                    doIncreaseEx(userid, 2);
                }
                result = 1;


            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit doSoil");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return 1;
    }

    public int watering(int userid, int landId) {
        int result = 0;
        try {

            log.info("Enter watering");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String strSQL = String.format("SELECT dry_percent,item_id,"
                        + "TIMESTAMPDIFF(MINUTE,time_dry,now())as dry, is_grown "
                        + "FROM PIDB.farm_land "
                        + "WHERE PIDB.farm_land.user_id = %d AND land_id=%d", userid, landId);


                ResultSet rs = s.executeQuery(strSQL);
                if (!rs.next()) {
                    result = -1;
                }

                int itemid = rs.getInt("item_id");
                int drypercent = rs.getInt("dry_percent");
                int percent = 0;
                if (itemid != 0) {

                    int dry = rs.getInt("dry"); // In Minute
                    int isgrown = rs.getInt("is_grown");
                    if (isgrown == 0) { // Allow Update only when not grown
                        if (dry > 5 && dry < 15) {
                            percent = 5;
                        } else if (dry > 15 && dry < 25) {
                            percent = 10;
                        } else if (dry > 25 && dry < 35) {
                            percent = 15;
                        } else if (dry > 35) {
                            percent = 20;
                        }

                        if (20 - drypercent < percent) {
                            percent = 20 - drypercent;
                        }
                    }
                }

                strSQL = String.format("UPDATE PIDB.farm_land SET "
                        + "time_dry=DATE_ADD(NOW(), INTERVAL 30 MINUTE),"
                        + "san_luong=san_luong-base_sanluong*%d/100,"
                        + "dry_percent=dry_percent+%d "
                        + "WHERE PIDB.farm_land.user_id = %d AND land_id=%d AND (TIMESTAMPDIFF(MINUTE,time_dry,now())>=0 OR ISNULL(TIMESTAMPDIFF(MINUTE,time_dry,now())) )", percent, percent, userid, landId);


                s.execute(strSQL);
                if (s.getUpdateCount() > 0) {
                    doIncreaseEx(userid, 2);
                }

                result = 1;
            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit watering");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();

        }
        return result;
    }

    public void createFarmUserIfNotExist(int userid) {
        try {

            log.info("Enter createFarmUserIfNotExist");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("INSERT INTO PIDB.farm_users SET user_id=%d", userid);
                s.execute(strSQL);
                if (s.getUpdateCount() > 0) {
                    for (int i = 1; i <= PIConstants.NUMBER_OF_LAND_CREATED_FOR_NEW_FARM_USER; i++) {
                        strSQL = String.format("INSERT INTO PIDB.farm_land SET user_id=%d,land_id=%d,"
                                + "time_dry=DATE_ADD(NOW(), INTERVAL -1 MINUTE),"
                                + "time_grass1=DATE_ADD(NOW(), INTERVAL 100 YEAR),"
                                + "time_grass2=DATE_ADD(NOW(), INTERVAL 100 YEAR),"
                                + "time_worm=DATE_ADD(NOW(), INTERVAL 100 YEAR)", userid, i);
                        s.execute(strSQL);
                    }
                }

            } catch (Exception ex) {
                log.info("User Farm Exist. Do not Create farm user");

            } finally {
                s.close();
                conn.close();
                log.info("Exit createFarmUserIfNotExist");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public void treatAnimal(int userid, int animalorder, int itemCategory, int itemType, String fieldupdate, int nexthour) {
        int itemId = getIDbyTypeCategory(itemType, itemCategory);
        if (countItemFarmStoreByID(userid, itemId, itemType) <= 0) {
            return;
        }

        /*
         * FEED HERE
         * 1.SET TIME HUNGRY= NEXT 12 hours
         */

        try {
            log.info("Enter treatAnimal");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("UPDATE PIDB.farm_animals SET "
                        + "%s=DATE_ADD(NOW(), INTERVAL %d HOUR) "
                        + "WHERE PIDB.farm_animals.user_id = %d  AND TIMESTAMPDIFF(MINUTE,%s,now())>0 AND id=%d", fieldupdate, nexthour, userid, fieldupdate, animalorder);
                s.execute(strSQL);
                if (s.getUpdateCount() > 0) {

                    useFarmStoreItem(userid, itemId, 1, itemType);

                    strSQL = String.format("SELECT level FROM PIDB.farm_animals WHERE user_id=%d AND id=%d", userid, animalorder);
                    ResultSet rs = s.executeQuery(strSQL);
                    if (rs.next()) {
                        int level = rs.getInt("level");
                        doIncreaseEx(userid, 4 * level);
                    }
                }

            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Treat Animals");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }



    }

    public void updateAnimalProduct(int userid) {
        try {


            log.info("Enter updateAnimalProduct");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                int money = 0;

                String strSQL = String.format("SELECT item_id,"
                        + "produce,base_produce,"
                        + "TIMESTAMPDIFF(MINUTE,farm_animals.time_born,farm_animals.time_grow) as grow,"
                        + "TIMESTAMPDIFF(MINUTE,now(),farm_animals.time_grow) as growleft,"
                        + "TIMESTAMPDIFF(MINUTE,time_death,now())as death,"
                        + "TIMESTAMPDIFF(MINUTE,time_tired,now())as tired,"
                        + "TIMESTAMPDIFF(MINUTE,time_hungry,now())as hungry,"
                        + "TIMESTAMPDIFF(MINUTE,time_have_flu,now())as haveflu,"
                        + "TIMESTAMPDIFF(MINUTE,time_headache,now())as headache,"
                        + "TIMESTAMPDIFF(MINUTE,last_time_collect,now()) as producemin "
                        + "FROM PIDB.farm_animals "
                        + "WHERE PIDB.farm_animals.user_id = %d ", userid);

                ResultSet rs = s.executeQuery(strSQL);

                while (rs.next()) {
                    int itemid = rs.getInt("item_id");
                    int producemin = rs.getInt("producemin");
                    int produceDays = producemin / (24 * 60);
                    int remainmin = producemin % (24 * 60);

                    int grow = rs.getInt("grow");
                    int growleft = rs.getInt("growleft") + remainmin;
                    int death = rs.getInt("death") - remainmin;
                    int tired = rs.getInt("tired") - remainmin;
                    int hungry = rs.getInt("hungry") - remainmin;
                    int haveflu = rs.getInt("haveflu") - remainmin;
                    int headache = rs.getInt("headache") - remainmin;

                    int product = rs.getInt("base_produce");


                    /*
                     * Kiem tra suc khoe qua nhieu ngay
                     */


                    int sanluong = 0;
                    int minaday = 24 * 60;
                    String lastsickoccur = "";


                    for (int day = 1; day <= produceDays; day++) { // Theo Ngay,


                        /*
                         * IF Death at this day. Stop get sanluong
                         */

                        if (death > (produceDays - day) * minaday) {
                            day = produceDays + 1;
                            break;
                        }

                        /*
                         * IF sick and Hungry for 2 days => Dead
                         */
                        if (((hungry - (produceDays - day) * minaday >= 2 * 24 * 60)
                                && (haveflu - (produceDays - day) * minaday >= 2 * 24 * 60))
                                || ((hungry - (produceDays - day) * minaday >= 2 * 24 * 60)
                                && (headache - (produceDays - day) * minaday >= 2 * 24 * 60))) {
                            day = produceDays + 1;

                            int temp = hungry;
                            lastsickoccur = "time_hungry";

                            if (temp > haveflu) {
                                lastsickoccur = "time_have_flu";
                                temp = haveflu;
                            }

                            if (temp > headache) {
                                lastsickoccur = "time_headache";
                            }
                            break;
                        }

                        /*
                         * IF Grow, collect
                         */


                        if (-growleft >= (produceDays - day) * minaday) { // IF GROW
                            int trangthai = 100;

                            // Check Tired
                            if (tired - (produceDays - day) * minaday > 0) {
                                trangthai -= 25;
                            }
                            // Check Hungry
                            if (hungry - (produceDays - day) * minaday > 0) {
                                trangthai -= 25;
                            }
                            // Check Flu
                            if (haveflu - (produceDays - day) * minaday > 0) {
                                trangthai -= 25;
                            }

                            if (headache - (produceDays - day) * minaday > 0) {
                                trangthai -= 25;
                            }
                            sanluong = sanluong + product * trangthai / 100;
                        }
                    }

                    if (!lastsickoccur.equals("")) {
                        strSQL = String.format("UPDATE PIDB.farm_animals SET time_death=DATE_ADD(%s, INTERVAL 2 DAY) WHERE PIDB.farm_animals.user_id = %d AND PIDB.farm_animals.item_id=%d", lastsickoccur, userid, itemid);
                        query(strSQL);
                    }

                    strSQL = String.format("UPDATE PIDB.farm_animals SET was_very_hungry=1 WHERE PIDB.farm_animals.user_id = %d AND PIDB.farm_animals.item_id=%d AND TIMESTAMPDIFF(MINUTE,time_hungry,now())>24*60", userid, itemid);
                    query(strSQL);

                    strSQL = String.format("UPDATE PIDB.farm_animals SET last_time_collect=DATE_ADD(NOW(), INTERVAL %d DAY), produce=produce+%d WHERE PIDB.farm_animals.user_id = %d AND PIDB.farm_animals.item_id=%d ", produceDays, sanluong, userid, itemid);
                    query(strSQL);


                }





            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit updateAnimalProduct");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

    }

    public int sellAnimals(int userid, int category, int orderid) {
        int result = 0;
        try {
            log.info("Enter sellAnimals");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("SELECT "
                        + "produce,base_produce,farm_animals.level,was_very_hungry,"
                        + "TIMEDIFF(farm_animals.time_grow,farm_animals.time_born) as grow,"
                        + "TIMESTAMPDIFF(MINUTE,now(),farm_animals.time_grow) as growleft,"
                        + "TIMEDIFF(now(),time_death) as death,"
                        + "TIMESTAMPDIFF(MINUTE,time_tired,now())as tired,"
                        + "TIMESTAMPDIFF(MINUTE,time_hungry,now())as hungry,"
                        + "TIMESTAMPDIFF(MINUTE,time_haveflu,now())as haveflu,"
                        + "TIMESTAMPDIFF(MINUTE,time_headache,now())as headache,"
                        + "price_sell,money,vip_money "
                        + "FROM PIDB.farm_animals "
                        + "INNER JOIN PIDB.farm_items ON PIDB.farm_items.item_id = PIDB.farm_animals.item_id "
                        + "WHERE PIDB.farm_animals.user_id = %d  AND TIMESTAMPDIFF(MINUTE,farm_animals.time_grow,now())>0  AND id=%d", userid, orderid);


                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {

                    int grow = rs.getInt("grow");
                    int growleft = rs.getInt("growleft");
                    int death = rs.getInt("death");
                    int tired = rs.getInt("tired");
                    int hungry = rs.getInt("hungry");
                    int haveflu = rs.getInt("haveflu");
                    int headache = rs.getInt("headache");
                    int produce = rs.getInt("produce");
                    int pricesell = rs.getInt("price_sell");
                    int money = rs.getInt("money");
                    int vipmoney = rs.getInt("vip_money");
                    int level = rs.getInt("level");
                    int wasveryhungry = rs.getInt("was_very_hungry");

                    int totalsell = 0;

                    int trangthai = 100;
                    if (tired > 0) {
                        trangthai -= 25;
                    }
                    if (hungry > 0) {
                        trangthai -= 25;
                    }
                    if (haveflu > 0) {
                        trangthai -= 25;
                    }
                    if (headache > 0) {
                        trangthai -= 25;
                    }

                    if (category == PIConstants.CATEGORY_FARM_IS_FOR_PIG) {
                        doIncreaseEx(userid, (produce * trangthai / 100) * level);
                        totalsell = (produce * trangthai / 100) * pricesell;

                    } else {
                        int percent = getGlobalProperties(PIConstants.STRING_SELL_ANIMAL_FEE);
                        if (money == 0 && vipmoney != 0) {
                            int exchangerate = getGlobalProperties(PIConstants.STRING_EXCHANGE_RATE);
                            money = vipmoney * exchangerate;
                        }
                        totalsell = (money * death / grow) * ((100 - percent) / 100);
                    }

                    if (wasveryhungry == 1) {
                        totalsell = totalsell * 3 / 4;
                    }
                    strSQL = String.format("DELETE FROM PIDB.farm_animals WHERE PIDB.farm_animals.user_id = %d   AND id=%d", userid, orderid);
                    query(strSQL);
                    strSQL = String.format("UPDATE PIDB.farm_users SET farm_money=farm_money+%d WHERE user_id=%d", totalsell, userid);
                    query(strSQL);
                    result = totalsell;
                }

            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit sellAnimals");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return result;
    }

    public int getGlobalProperties(String name) {
        int result = 0;
        try {
            log.info("Enter getGlobalProperties");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String strSQL = String.format("SELECT property_value "
                        + "FROM PIDB.global_properties "
                        + "WHERE property_name='%s'", name);

                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    String value = rs.getString("property_value");
                    result = Integer.parseInt(value);
                }

            } catch (Exception ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Insert Farm Store");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();

        }
        return result;
    }

    public int collectAnimalProduct(int userID, int itemCategory) {
        int result = 0;
        /*
         * TODO: Chuyen tu Sua sang Tien
         */
        try {

            log.info("Enter collectAnimalProduct");
            Connection conn = getConnection();
            Statement s = conn.createStatement();

            try {

                String strSQL = String.format("SELECT SUM(produce) as sumproduce "
                        + "FROM PIDB.farm_animals"
                        + "WHERE PIDB.farm_animals.user_id = %d AND PIDB.farm_animals.item_id IN("
                        + "SELECT item_id FROM PIDB.farm_items WHERE type=%d AND category=%d)", userID, PIConstants.TYPE_FARM_ANIMAL, itemCategory);
                ResultSet rs = s.executeQuery(strSQL);

                result = 0;
                while (rs.next()) {
                    result += rs.getInt("sumproduce");
                }

                strSQL = String.format("UPDATE PIDB.farm_animals SET produce=0 "
                        + "WHERE PIDB.farm_animals.user_id = %d AND PIDB.farm_animals.item_id IN("
                        + "SELECT item_id FROM PIDB.farm_items WHERE type=%d AND category=%d)", userID, PIConstants.TYPE_FARM_ANIMAL, itemCategory);
                query(strSQL);
                strSQL = String.format("DELETE FROM PIDB.farm_animals WHERE "
                        + "farm_animals.user_id = %d  AND "
                        + "TIMESTAMPDIFF(MINUTE,time_death,now())>0", userID);
                query(strSQL);
                int productid = getIDbyTypeCategory(PIConstants.TYPE_FARM_ANIMAL_PRODUCT, itemCategory);
                insertFarmStore(userID, productid, result, PIConstants.TYPE_FARM_ANIMAL_PRODUCT);


            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit collectAnimalProduct");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return result;
    }

    public void buyLand(int userid, int price) {
        try {
            log.info("Enter buyLand");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String strSQL = String.format("SELECT MAX(land_id) as landid "
                        + "FROM PIDB.farm_land "
                        + "WHERE user_id = %d", userid);

                ResultSet rs = s.executeQuery(strSQL);
                rs.next();
                int landid = rs.getInt("landid") + 1;
                strSQL = String.format("INSERT INTO PIDB.farm_land SET user_id=%d,land_id=%d,"
                        + "time_dry=DATE_ADD(NOW(), INTERVAL -1 MINUTE),"
                        + "time_grass1=DATE_ADD(NOW(), INTERVAL 100 YEAR),"
                        + "time_grass2=DATE_ADD(NOW(), INTERVAL 100 YEAR),"
                        + "time_worm=DATE_ADD(NOW(), INTERVAL 100 YEAR)", userid, landid);
                query(strSQL);
                strSQL = String.format("UPDATE PIDB.farm_users SET farm_money=farm_money-%d WHERE user_id=%d", price, userid);
                query(strSQL);

            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit buyLand");
            }

        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
    }

    /*
     * Shop
     */
    public ArrayList getFarmItems(int type) {
        ArrayList result = new ArrayList();
        try {
            log.info("Enter getFarmItems");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {


                String strSQL = String.format("SELECT * "
                        + "FROM PIDB.farm_items "
                        + "WHERE type = %d", type);

                ResultSet rs = s.executeQuery(strSQL);
                while (rs.next()) {
                    FarmStuff stuff = new FarmStuff();
                    stuff.id = rs.getInt("item_id");
                    stuff.type = type;
                    stuff.name = rs.getString("item_name");
                    stuff.description = rs.getString("description");
                    stuff.money = rs.getInt("money");
                    stuff.vipmoney = rs.getInt("vip_money");
                    stuff.timegrow = rs.getInt("time_grow");
                    stuff.income = rs.getInt("product") * rs.getInt("price_sell");
                    result.add(stuff);
                }

            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Insert Farm Store");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

        return result;
    }

    public ArrayList getFarmStore(int userid, int type) {
        ArrayList result = new ArrayList();
        try {
            log.info("Enter getFarmStore");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("SELECT amount,farm_store.item_id,item_name,category "
                        + "FROM PIDB.farm_store "
                        + "INNER JOIN PIDB.farm_items ON farm_items.item_id=farm_store.item_id "
                        + "WHERE user_id=%d AND  PIDB.farm_store.type=%d ", userid, type);

                ResultSet rs = s.executeQuery(strSQL);
                while (rs.next()) {
                    FarmStuff stuff = new FarmStuff();
                    stuff.id = rs.getInt("item_id");
                    stuff.amount = rs.getInt("amount");
                    stuff.name = rs.getString("item_name");
                    stuff.category = rs.getInt("category");
                    result.add(stuff);
                }
            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit Insert Farm Store");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }

        return result;
    }

    public int getFarmItemPrice(int itemid) {
        int result = -1;
        try {

            log.info("Enter getFarmItemPrice");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String strSQL = String.format("SELECT money FROM PIDB.farm_items WHERE item_id=%d", itemid);
                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    result = rs.getInt("money");
                }
            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit getFarmItemPrice");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
            e.printStackTrace();
        }
        return result;
    }

    public void buyFarmItems(int userid, int itemId, int amount) {
        try {

            log.info("Enter buyFarmItems");
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {

                String strSQL = String.format("SELECT * FROM PIDB.farm_items WHERE item_id=%d", itemId);
                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    int money = rs.getInt("money") * amount;
                    int type = rs.getInt("type");
                    if (rs.getInt("time_life") == 0) { // Thuc vat hoac vat pham
                        insertFarmStore(userid, itemId, amount, type);
                    } else { // Dong Vat
                        int timegrow = rs.getInt("time_grow"); // In Hour
                        int timelife = rs.getInt("time_life"); // In Day
                        int product = rs.getInt("product");
                        int level = rs.getInt("level");

                        for (int i = 0; i < amount; i++) {
                            int timetired = 24 + CommonUtils.random(24); // IN HOUR
                            int timehungry = 12 + CommonUtils.random(12);
                            int timehaveflu = 24 + CommonUtils.random(24);
                            int timeheadache = 24 + CommonUtils.random(24);

                            strSQL = String.format("INSERT INTO PIDB.farm_animals SET "
                                    + "user_id=%d,item_id=%d,level=%d,"
                                    + "time_grow=DATE_ADD(NOW(), INTERVAL %d HOUR),"
                                    + "time_born=NOW(),"
                                    + "last_time_collect=NOW(),"
                                    + "time_death=DATE_ADD(NOW(), INTERVAL %d DAY),"
                                    + "base_produce=%d,produce=%d,"
                                    + "time_tired=DATE_ADD(NOW(), INTERVAL %d HOUR),"
                                    + "time_hungry=DATE_ADD(NOW(), INTERVAL %d HOUR),"
                                    + "time_have_flu=DATE_ADD(NOW(), INTERVAL %d HOUR),"
                                    + "time_headache=DATE_ADD(NOW(), INTERVAL %d HOUR)", userid, itemId, level, timegrow, timelife, product, product, timetired, timehungry, timehaveflu, timeheadache);

                            query(strSQL);
                        }

                    }

                    strSQL = String.format("UPDATE PIDB.farm_users SET farm_money=farm_money-%d WHERE user_id=%d", money, userid);
                    query(strSQL);
                }


            } catch (SQLException ex) {
                log.error("Get Statement error : ", ex.getClass().getName(), ex);
                ex.printStackTrace();
            } finally {
                s.close();
                conn.close();
                log.info("Exit buyFarmItems");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public void sellFarmProduct(int userid, int itemid) {
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            String strSQL = String.format("SELECT amount,price_sell "
                    + "FROM PIDB.farm_store "
                    + "INNER JOIN PIDB.farm_items ON farm_items.itemid=farm_store.item_id "
                    + "WHERE user_id=%d AND item_id=%d AND PIDB.farm_store.type=%d", userid, itemid, PIConstants.TYPE_FARM_ANIMAL_PRODUCT);
            try {
                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    int amount = rs.getInt("amount");
                    int pricesell = rs.getInt("price_sell");

                    strSQL = String.format("UPDATE PIDB.farm_users SET farm_money=farm_money+%d WHERE user_id=%d", amount * pricesell, userid);
                    query(strSQL);
                    useFarmStoreItem(userid, itemid, amount, PIConstants.TYPE_FARM_ANIMAL_PRODUCT);
                }
            } catch (Exception e) {
                log.error("Get Statement error : ", e.getClass().getName(), e);
            } finally {
                s.close();
                conn.close();
                log.info("Exit buyFarmItems");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
    }

    public InputStream getItemFarmImage(int itemID) {
        InputStream stream = null;
        try {

            Connection conn = getConnection();
            Statement s = conn.createStatement();
            try {
                String strSQL = String.format("SELECT image from PIDB.farm_items where item_id=%d", itemID);
                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    stream = rs.getBinaryStream("image");
                }
            } catch (Exception ex) {
                log.error(ex.getClass().getName(), ex);
            } finally {
                s.close();
                conn.close();
                log.info("Exit getAvatarById");
            }
        } catch (Exception e) {
            log.error("Get Statement error : ", e.getClass().getName(), e);
        }
        return stream;
    }

    /*
     * Experice and Level
     */
    private int gettotalex(int nextlevel) {
        if (nextlevel == 1) {
            return 10;
        } else {
            return 4 * nextlevel * nextlevel + gettotalex(nextlevel - 1);
        }
    }

    private void doIncreaseEx(int userid, int exincrease) {
        try {
            Connection conn = getConnection();
            Statement s = conn.createStatement();
            String strSQL = String.format("SELECT farm_level,farm_percent FROM PIDB.farm_users WHERE user_id=%d", userid);
            try {
                ResultSet rs = s.executeQuery(strSQL);
                if (rs.next()) {
                    int levelincrease = 0;
                    int farmlevel = rs.getInt("farm_level");
                    int farmpercent = rs.getInt("farm_percent");
                    int getNeededEx = gettotalex(farmlevel + 1);
                    if (farmpercent + exincrease >= getNeededEx) {
                        levelincrease = 1;
                    }
                    strSQL = String.format("UPDATE PIDB.farm_users SET farm_level=farm_level+%d,farm_percent=farm_percent+%d WHERE user_id=%d", levelincrease, exincrease, userid);
                    query(strSQL);
                }
            } catch (SQLException ex) {
            } finally {
                s.close();
                conn.close();
                log.info("Exit getAvatarById");


            }
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(MySQLCommonDataProvider.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
